Functions to add and retrieve metadata to postgresql database

data_dictionary_postgresql.R

#' @title Generate Data Dictionary Table
#'
#' @param table character table name
#' @param schema character schema name
#' @param db
#' @param con database connection object
#' @param ...
#'
#' @importFrom DBI dbGetQuery dbDisconnect
#' @importFrom getdata con_postgresql
#' @importFrom glue glue
#' @importFrom dplyr left_join mutate
#'
#' @return data.frame
#' @export
#'
#' @examples
#' data_dict('rrt_sites')
data_dict <- function(table, schema = 'public', db = 'postgresql', con = NULL, ...){

  DELETE_CON <- FALSE

  if (is.null(con)) {
    con <- con_postgresql()
    DELETE_CON <- TRUE
  }


  dict <- paste('select ordinal_position as position,',
                      'column_name,',
                      'data_type,',
                      'case when character_maximum_length is not null',
                      'then character_maximum_length',
                      'else numeric_precision end as max_length,',
                      'is_nullable,',
                      'column_default as default_value',
                      'from information_schema.columns',
                      glue("where table_name = '{table}'"),
                      glue("and table_schema = '{schema}'"),
                      'order by ordinal_position;',
                      sep = ' ') %>%
    DBI::dbGetQuery(con, .)

  descriptions <- paste('SELECT c.table_schema,c.table_name,c.column_name,pgd.description',
                        'FROM pg_catalog.pg_statio_all_tables as st',
                        'inner join pg_catalog.pg_description pgd on (pgd.objoid=st.relid)',
                        'inner join information_schema.columns c on (pgd.objsubid=c.ordinal_position',
                        'and  c.table_schema=st.schemaname and c.table_name=st.relname)',
                        glue("WHERE c.table_name = '{table}'"),
                        glue("AND c.table_schema = '{schema}'"),
                        sep = ' ') %>%
    DBI::dbGetQuery(con, .)

  if (DELETE_CON) DBI::dbDisconnect(con)

  if (nrow(descriptions) > 0) {
    data_dict <- left_join(dict, descriptions, by = 'column_name') %>%
      mutate(table_schema = schema,
             table_name = table)
  } else {
    data_dict <- dict %>%
      mutate(table_schema = schema,
             table_name = table,
             description = as.character(NA))
  }

  return(data_dict)

}



#' @title Add field description to database
#'
#' @param table character table name
#' @param field character field name
#' @param description character description of field
#' @param schema character schema name
#' @param db
#' @param con database connection object
#' @param ...
#'
#' @export
#'
#' @examples
add_field_description <- function(table, field, description, schema = 'public', db = 'postgresql', con = NULL, ...){

  DELETE_CON <- FALSE

  if (is.null(con)) {
    con <- con_postgresql()
    DELETE_CON <- TRUE
  }

  glue("comment on column {schema}{table}.{field} is '{description}';") %>%
     DBI::dbSendQuery(con, .)

  if (DELETE_CON) DBI::dbDisconnect(con)

}