Retrieving information about tables and views in Greenplum

posted Apr 28, 2017, 10:38 AM by Sachchida Ojha
-- 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)
Comments