ruut/R/db_table_colnames.R

#' Database table columns info
#'
#' Andmebaasi tabel veergude nimekiri.
#' @param conf	list List of configuration data. Rewrite it.
#' @keywords database, table
#' @export
#' @examples
#' config <- get_config()
#' config$schema <- "public"
#' config$table <- "spatial_ref_sys"
#' db_table_colnames(conf = config)
db_table_colnames <- function(conf) {
  conn <- ruut::db_connect()
  q <- sprintf("SELECT
    pg_attribute.attname AS column_name,
    pg_catalog.format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS data_type
FROM
    pg_catalog.pg_attribute
INNER JOIN
    pg_catalog.pg_class ON pg_class.oid = pg_attribute.attrelid
INNER JOIN
    pg_catalog.pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE
    pg_attribute.attnum > 0
    AND NOT pg_attribute.attisdropped
    AND pg_namespace.nspname = '%s'
    AND pg_class.relname = '%s'
ORDER BY
    attnum ASC;", conf$schema, conf$table)
  res <- DBI::dbGetQuery(conn, q)
  # Disconnect
  lapply(DBI::dbListConnections(RPostgreSQL::PostgreSQL()), DBI::dbDisconnect)
  res
}