RE: [pgsql-es-ayuda] consultas útiles al catalogo

Esto es lo que yo hago cuando necesito conocer algunas consultas para acceder al catálogo. Espero te sirva.

[root(at)postgresql ~]# su - postgres -c "psql -E test"

psql (9.2.1)

Type "help" for help.

test=# \dt

********* QUERY **********

SELECT n.nspname as "Schema",

c.relname as "Name",

CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",

pg_catalog.pg_get_userbyid(c.relowner) as "Owner"

FROM pg_catalog.pg_class c

LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE c.relkind IN ('r','')

AND n.nspname <> 'pg_catalog'

AND n.nspname <> 'information_schema'

AND n.nspname !~ '^pg_toast'

AND pg_catalog.pg_table_is_visible(c.oid)

ORDER BY 1,2;

**************************

List of relations

Schema | Name | Type | Owner

--------+------+-------+----------

public | test | table | postgres

(1 row)

test=# \d test

********* QUERY **********

SELECT c.oid,

n.nspname,

c.relname

FROM pg_catalog.pg_class c

LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE c.relname ~ '^(test)$'

AND pg_catalog.pg_table_is_visible(c.oid)

ORDER BY 2, 3;

**************************

********* QUERY **********

SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoids, '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence

FROM pg_catalog.pg_class c

LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)

WHERE c.oid = '38057';

**************************

********* QUERY **********

SELECT a.attname,

pg_catalog.format_type(a.atttypid, a.atttypmod),

(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)

FROM pg_catalog.pg_attrdef d

WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),

a.attnotnull, a.attnum,

(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t

WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,

NULL AS indexdef,

NULL AS attfdwoptions

FROM pg_catalog.pg_attribute a

WHERE a.attrelid = '38057' AND a.attnum > 0 AND NOT a.attisdropped

ORDER BY a.attnum;

**************************

********* QUERY **********

SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '38057' ORDER BY inhseqno;

**************************

********* QUERY **********

SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '38057' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;

**************************

Table "public.test"

Column | Type | Modifiers

--------+---------+---------------------------------------------------

id | integer | not null default nextval('test_id_seq'::regclass)

cadena | text |

test=#

From: pgsql-es-ayuda-owner(at)postgresql(dot)org [mailto:pgsql-es-ayuda-owner(at)postgresql(dot)org] On Behalf Of Arcel Labrada Batista
Sent: Wednesday, March 27, 2013 4:24 PM
Cc: pgsql-es-ayuda(at)postgresql(dot)org
Subject: [pgsql-es-ayuda] consultas útiles al catalogo

buenas tardes,
necesito algún documento con consultas utiles al catalogo de postgres para tenerlo, y por si no existe necesito de manera un poco mas urgente una consulta que me devuelva algo como lo siguiente

nombre_tabla columnas
mitabla_1 col1t1,col2t1,col3t1
mitabla_2 col1t2,col2t2,col3t2

donde col1t1,col2t1,col3t1 son las columnas de mitabla_1, gracias de antemano

<http://www.uci.cu/> Description: Image removed by sender.

<http://www.uci.cu/> Description: Image removed by sender.

http://www.uci.cu