R PostgreSQL Helpers

postgres_helpers.R

# Get all indexes, then drop and add before and after writing to table
# Speeds up writes
get_index_table <- function(con, tablename, schemaname) {
  index_df <- dbGetQuery(con, paste0(
    "SELECT indexname, indexdef FROM pg_indexes
    WHERE tablename = '", tablename, "' AND schemaname = '", schemaname, "'"))
  
  index_df <- subset(index_df, !grepl("[pf]key", indexname))
  
  index_df$indexname <- paste0(schemaname, ".", index_df$indexname)
  
  return(index_df)
}

drop_indexes <- function(con, indexes) {
  for(index in indexes) dbExecute(con, paste("DROP INDEX IF EXISTS ", index))
}

restore_indexes <- function(con, index_def_list) {
  for(def in index_def_list) dbExecute(con, def)
}