recuperar - usuarios y roles en postgresql

Recuperando comentarios de una base de datos PostgreSQL

Question

Estoy ejecutando un proyecto en una base de datos de Postgres y necesito recuperar los comentarios en las columnas dentro del DB para usarlos como encabezados de tablas y cosas por el estilo. He visto que hay un par de funciones integradas ( pg_description y col_description ) pero no he podido encontrar ejemplos sobre cómo usarlas y jugar con ellas ha resultado bastante inútil.

Entonces, me preguntaba si alguien ha podido hacer esto antes y, de ser así, ¿cómo?

Answer 1

Genial, eso funciona para hacer aparecer un solo comentario, pero ¿hay alguna manera de mostrar todos los comentarios de todas las columnas sin múltiples declaraciones de selección o un ciclo?

¿Y cómo se lanza esto con una declaración?

Si quiere saber qué consultas ejecuta psql cuando hace / dt + o / d + clientes, simplemente lance con''E.

Gracias

Answer 2

Ok, entonces lo resolví en grado ...

seleccione col_description (identificación de la tabla, número de columna) ...

es decir: seleccione col_description (36698,2);

Eso funcionó, pero ¿hay una forma más fácil de hacer esto, tal vez traer todos los comentarios en todas las columnas y usar el nombre de la tabla en lugar del oid?

Answer 3

Esto funciona para mí usando la base de datos PostBooks 3.2.2:

select cols.column_name,
(select pg_catalog.obj_description(oid) from pg_catalog.pg_class c where c.relname=cols.table_name) as table_comment
,(select pg_catalog.col_description(oid,cols.ordinal_position::int) from pg_catalog.pg_class c where c.relname=cols.table_name) as column_comment
from information_schema.columns cols
where cols.table_catalog=''postbooks'' and cols.table_name=''apapply''

Saludos, Sylnsr

Answer 4

Esta respuesta es un poco tarde, pero apareció en una búsqueda en Google que hice para investigar este problema. Solo necesitábamos descripciones de tabla, pero el método sería el mismo para las columnas. Las descripciones de las columnas están en la tabla pg_description también, referenciadas por objoid.

Añadir esta vista:

CREATE OR REPLACE VIEW our_tables AS 
 SELECT c.oid, n.nspname AS schemaname, c.relname AS tablename, d.description,
   pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS "tablespace", 
   c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.reltriggers > 0 AS hastriggers
   FROM pg_class c
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
   LEFT JOIN pg_description d ON c.oid = d.objoid
  WHERE c.relkind = ''r''::"char";

ALTER TABLE our_tables OWNER TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE our_tables TO postgres;
GRANT SELECT ON TABLE our_tables TO public;

Entonces corre:

SELECT tablename, description FROM our_tables WHERE schemaname = ''public''

La vista es una versión modificada de la vista pg_tables que se agrega en la columna de descripción. También podría pasear con la definición de la vista para convertirla en una sola consulta.

Answer 5

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);

Answer 6

Todo funciona por oid,

mat=> SELECT c.oid FROM pg_catalog.pg_class c WHERE c.relname = ''customers'';
  oid  
-------
 23208
(1 row)

Ahora, tengo el OID para esa tabla, entonces puedo preguntar:

mat=> select pg_catalog.obj_description(23208);
  obj_description  
-------------------
 Customers
(1 row)

Entonces, puedo pedir la descripción de la cuarta columna:

mat=> select pg_catalog.col_description(23208,4);
             col_description             
-----------------------------------------
 Customer codes, CHS, FACTPOST, POWER...
(1 row)

Si quiere saber qué consultas ejecuta psql cuando hace /dt+ o /d+ customers , simplemente ejecútelo con -E .

Answer 7

Solo para estar aquí si alguien lo necesita.

Aquí hay muchas respuestas, pero ninguna fue tan simple como me gustaría que fuera. Entonces, en base a las respuestas anteriores y al postgres actual 9.4, he creado esta consulta:

SELECT 
    obj_description(format(''%s.%s'',isc.table_schema,isc.table_name)::regclass::oid, ''pg_class'') as table_description,
    pg_catalog.col_description(format(''%s.%s'',isc.table_schema,isc.table_name)::regclass::oid,isc.ordinal_position) as column_description
FROM
    information_schema.columns isc

Obtiene las descripciones de tabla y columna, sin combinaciones confusas y concatenaciones de cadenas feas.

Answer 8

Acabo de encontrar esto aquí. Le proporcionará todo tipo de metadatos en una tabla específica (tipo, valor predeterminado, indicador nulo, longitud, comentario, nombre de clave externa, nombre de clave principal). Parece que funciona bien.

SELECT pg_tables.tablename, pg_attribute.attname AS field, 
    format_type(pg_attribute.atttypid, NULL) AS "type", 
    pg_attribute.atttypmod AS len,
    (SELECT col_description(pg_attribute.attrelid, 
            pg_attribute.attnum)) AS comment, 
    CASE pg_attribute.attnotnull 
        WHEN false THEN 1  ELSE 0  
    END AS "notnull", 
    pg_constraint.conname AS "key", pc2.conname AS ckey, 
    (SELECT pg_attrdef.adsrc FROM pg_attrdef 
        WHERE pg_attrdef.adrelid = pg_class.oid 
        AND pg_attrdef.adnum = pg_attribute.attnum) AS def 
FROM pg_tables, pg_class 
JOIN pg_attribute ON pg_class.oid = pg_attribute.attrelid 
    AND pg_attribute.attnum > 0 
LEFT JOIN pg_constraint ON pg_constraint.contype = ''p''::"char" 
    AND pg_constraint.conrelid = pg_class.oid AND
    (pg_attribute.attnum = ANY (pg_constraint.conkey)) 
LEFT JOIN pg_constraint AS pc2 ON pc2.contype = ''f''::"char" 
    AND pc2.conrelid = pg_class.oid 
    AND (pg_attribute.attnum = ANY (pc2.conkey)) 
WHERE pg_class.relname = pg_tables.tablename  
--    AND pg_tables.tableowner = "current_user"() 
    AND pg_attribute.atttypid <> 0::oid  
    AND tablename=''your_table'' 
ORDER BY field ASC

Fuente: http://golden13.blogspot.de/2012/08/how-to-get-some-information-about_7.html

Answer 9

Hice una pregunta similar sobre los comentarios de Postgresql el mes pasado. Si profundizas en eso, encontrarás algún código de Perl en mi blog que automatiza el proceso de extracción de un comentario.

Para extraer los nombres de las columnas de una tabla, puede usar algo como lo siguiente:

select
     a.attname  as "colname"
    ,a.attrelid as "tableoid"
    ,a.attnum   as "columnoid"
from
    pg_catalog.pg_attribute a
    inner join pg_catalog.pg_class c on a.attrelid = c.oid
where
        c.relname = ''mytable'' -- better to use a placeholder
    and a.attnum > 0
    and a.attisdropped is false
    and pg_catalog.pg_table_is_visible(c.oid)
order by a.attnum

A continuación, puede usar el tableoide, una tupla columnoide para extraer el comentario de cada columna (vea mi pregunta).

Answer 10

Mejora para las sugerencias @Nick y @mat: uso
SELECT obj_description(''schemaName.tableName''::regclass, ''pg_class''); cuando tienes el nombre de la cadena (no oid).

Para evitar recordar el parámetro ''pg_class'', y para evitar concatenaciones desagradables en las llamadas a funciones, como (tname||''.''||schema)::regclass , una sobrecarga útil para obj_description :

  CREATE FUNCTION obj_description(
      p_rname text, p_schema text DEFAULT NULL, 
      p_catalname text DEFAULT ''pg_class''
  ) RETURNS text AS $f$
     SELECT obj_description((CASE 
        WHEN strpos($1, ''.'')>0 OR $2 IS NULL OR $2='''' THEN $1
        ELSE $2||''.''||$1
     END)::regclass, $3);
  $f$ LANGUAGE SQL IMMUTABLE;
 -- USAGE: obj_description(''mytable'') 
 --        SELECT obj_description(''s.t''); 
 -- PS: obj_description(''s.t'', ''otherschema'') is a syntax error, 
 --     but not generates exception: returns the same as (''s.t'') 

Ahora es fácil de usar, porque el nombre de la tabla (parámetro rname ) es varchar y se puede expresar con un campo separado para el nombre del esquema , como en las tablas principales y las consultas.

Consulte también " Obtención de la lista de comentarios de tabla en PostgreSQL " o la nueva Guía de pg9.3

Answer 11

Tenga cuidado con los esquemas, este código los considera:

SELECT
    cols.column_name,
    (
        SELECT
            pg_catalog.col_description(c.oid, cols.ordinal_position::int)
        FROM
            pg_catalog.pg_class c
        WHERE
            c.oid = (SELECT (''"'' || cols.table_name || ''"'')::regclass::oid)
            AND c.relname = cols.table_name
    ) AS column_comment
FROM
    information_schema.columns cols
WHERE
    cols.table_catalog    = ''your_database''
    AND cols.table_name   = ''your_table''
    AND cols.table_schema = ''your_schema'';

Referencias

• Tabla de documentos Postgresql y descripción de columnas Comentarios sobre tabla y columna

• Determinar el OID de una tabla en Postgres 9.1?

Answer 12

Accedí a los comentarios de la tabla como este:

select c.relname table_name, pg_catalog.obj_description(c.oid) as comment from pg_catalog.pg_class c where c.relname = ''table_name'';

y la columna comenta así:

SELECT 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 and c.table_name = ''table_name'' and c.table_schema = ''public'');