ruut/R/db_table_colnames.R

#' Database table columns info
#'
#' Andmebaasi tabel veergude nimekiri.
#' @param conf A list() of configuration variables. Default values \code{\link[ruut]{get_config}}.
#' @seealso [ruut::db_connect()], [ruut::get_config()]
#' @keywords database, table
#' @export
#' @examples
#' ## Not run:
#' ##
#' ## config <- get_config()
#' ## config$schema <- "public"
#' ## config$table <- "spatial_ref_sys"
#' ## db_table_colnames(conf = config)
#' ##
#' ## End(**Not run**)
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
}