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)