Retrieving information about tables and views

Post date: Oct 19, 2014 10:14:12 PM

-- Table

SELECT 

n.nspname AS schemaname, 

c.relname AS tablename, 

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 

 WHERE c.relkind = 'r'::"char";

--Index

SELECT 

n.nspname AS schemaname, 

c.relname AS tablename, 

i.relname AS indexname, 

t.spcname AS tablespace, 

pg_get_indexdef(i.oid) AS indexdef

FROM pg_index x

JOIN pg_class c ON c.oid = x.indrelid

JOIN pg_class i ON i.oid = x.indexrelid

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

LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace

WHERE c.relkind = 'r'::"char" AND i.relkind = 'i'::"char";

-- Table Distribution Key

SELECT pgn.nspname as schemaname, 

pgc.relname as tablename, 

pga.attname  as distributionkey

FROM (SELECT gdp.localoid, 

CASE 

WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN 

Unnest(gdp.attrnums) 

ELSE NULL 

END AS attnum 

FROM gp_distribution_policy gdp 

ORDER BY gdp.localoid) AS distrokey 

INNER JOIN pg_class AS pgc 

ON distrokey.localoid = pgc.oid 

INNER JOIN pg_namespace pgn 

ON pgc.relnamespace = pgn.oid 

LEFT OUTER JOIN pg_attribute pga 

ON distrokey.attnum = pga.attnum 

AND distrokey.localoid = pga.attrelid 

ORDER BY pgn.nspname, 

pgc.relname;

-- Important pg_get functions

sachi=# \df pg_get*

List of functions

Schema | Name | Result data type | Argument data types | Type

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

pg_catalog | pg_get_constraintdef | text | oid | normal

pg_catalog | pg_get_constraintdef | text | oid, boolean | normal

pg_catalog | pg_get_expr | text | text, oid | normal

pg_catalog | pg_get_expr | text | text, oid, boolean | normal

pg_catalog | pg_get_indexdef | text | oid | normal

pg_catalog | pg_get_indexdef | text | oid, integer, boolean | normal

pg_catalog | pg_get_keywords | SETOF record | OUT word text, OUT catcode "char", OUT catdesc text | normal

pg_catalog | pg_get_partition_def | text | oid | normal

pg_catalog | pg_get_partition_def | text | oid, boolean | normal

pg_catalog | pg_get_partition_def | text | oid, boolean, boolean | normal

pg_catalog | pg_get_partition_rule_def | text | oid | normal

pg_catalog | pg_get_partition_rule_def | text | oid, boolean | normal

pg_catalog | pg_get_partition_template_def | text | oid, boolean, boolean | normal

pg_catalog | pg_get_ruledef | text | oid | normal

pg_catalog | pg_get_ruledef | text | oid, boolean | normal

pg_catalog | pg_get_serial_sequence | text | text, text | normal

pg_catalog | pg_get_triggerdef | text | oid | normal

pg_catalog | pg_get_userbyid | name | oid | normal

pg_catalog | pg_get_viewdef | text | oid | normal

pg_catalog | pg_get_viewdef | text | oid, boolean | normal

pg_catalog | pg_get_viewdef | text | text | normal

pg_catalog | pg_get_viewdef | text | text, boolean | normal

(22 rows)