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