–DROP VIEW admin.v_generate_tbl_ddl; /********************************************************************************************** Purpose: View to get the DDL for a table. This will contain the distkey, sortkey, constraints,
not null, defaults, etc.
Notes: Default view ordering causes foreign keys to be created at the end.
This is needed due to dependencies of the foreign key constraint and the tables it links. Due to this one should not manually order the output if you are expecting to be able to replay the SQL directly from the VIEW query result. It is still possible to order if you filter out the FOREIGN KEYS and then apply them later. The following filters are useful: where ddl not like 'ALTER TABLE %' -- do not return FOREIGN KEY CONSTRAINTS where ddl like 'ALTER TABLE %' -- only get FOREIGN KEY CONSTRAINTS where tablename in ('t1', 't2') -- only get DDL for specific tables where schemaname in ('s1', 's2') -- only get DDL for specific schemas So for example if you want to order DDL on tablename and only want the tables 't1', 't2' and 't4' you can do so by using a query like: select ddl from ( ( select * from admin.v_generate_tbl_ddl where ddl not like 'ALTER TABLE %' order by tablename ) UNION ALL ( select * from admin.v_generate_tbl_ddl where ddl like 'ALTER TABLE %' order by tablename ) ) where tablename in ('t1', 't2', 't4');
History: 2014-02-10 jjschmit Created 2015-05-18 ericfe Added support for Interleaved sortkey 2015-10-31 ericfe Added cast tp increase size of returning constraint name 2016-05-24 chriz-bigdata Added support for BACKUP NO tables 2017-05-03 pvbouwel Change table & schemaname of Foreign key constraints to allow for filters 2018-01-15 pvbouwel Add QUOTE_IDENT for identifiers (schema,table and column names) **********************************************************************************************/ CREATE OR REPLACE VIEW admin.v_generate_tbl_ddl AS SELECT
REGEXP_REPLACE (schemaname, '^zzzzzzzz', '') AS schemaname ,REGEXP_REPLACE (tablename, '^zzzzzzzz', '') AS tablename ,seq ,ddl
FROM
( SELECT schemaname ,tablename ,seq ,ddl FROM ( --DROP TABLE SELECT n.nspname AS schemaname ,c.relname AS tablename ,0 AS seq ,'--DROP TABLE ' + QUOTE_IDENT(n.nspname) + '.' + QUOTE_IDENT(c.relname) + ';' AS ddl FROM pg_namespace AS n INNER JOIN pg_class AS c ON n.oid = c.relnamespace WHERE c.relkind = 'r' --CREATE TABLE UNION SELECT n.nspname AS schemaname ,c.relname AS tablename ,2 AS seq ,'CREATE TABLE IF NOT EXISTS ' + QUOTE_IDENT(n.nspname) + '.' + QUOTE_IDENT(c.relname) + '' AS ddl FROM pg_namespace AS n INNER JOIN pg_class AS c ON n.oid = c.relnamespace WHERE c.relkind = 'r' --OPEN PAREN COLUMN LIST UNION SELECT n.nspname AS schemaname, c.relname AS tablename, 5 AS seq, '(' AS ddl FROM pg_namespace AS n INNER JOIN pg_class AS c ON n.oid = c.relnamespace WHERE c.relkind = 'r' --COLUMN LIST UNION SELECT schemaname ,tablename ,seq ,'\t' + col_delim + col_name + ' ' + col_datatype + ' ' + col_nullable + ' ' + col_default + ' ' + col_encoding AS ddl FROM ( SELECT n.nspname AS schemaname ,c.relname AS tablename ,100000000 + a.attnum AS seq ,CASE WHEN a.attnum > 1 THEN ',' ELSE '' END AS col_delim ,QUOTE_IDENT(a.attname) AS col_name ,CASE WHEN STRPOS(UPPER(format_type(a.atttypid, a.atttypmod)), 'CHARACTER VARYING') > 0 THEN REPLACE(UPPER(format_type(a.atttypid, a.atttypmod)), 'CHARACTER VARYING', 'VARCHAR') WHEN STRPOS(UPPER(format_type(a.atttypid, a.atttypmod)), 'CHARACTER') > 0 THEN REPLACE(UPPER(format_type(a.atttypid, a.atttypmod)), 'CHARACTER', 'CHAR') ELSE UPPER(format_type(a.atttypid, a.atttypmod)) END AS col_datatype ,CASE WHEN format_encoding((a.attencodingtype)::integer) = 'none' THEN '' ELSE 'ENCODE ' + format_encoding((a.attencodingtype)::integer) END AS col_encoding ,CASE WHEN a.atthasdef IS TRUE THEN 'DEFAULT ' + adef.adsrc ELSE '' END AS col_default ,CASE WHEN a.attnotnull IS TRUE THEN 'NOT NULL' ELSE '' END AS col_nullable FROM pg_namespace AS n INNER JOIN pg_class AS c ON n.oid = c.relnamespace INNER JOIN pg_attribute AS a ON c.oid = a.attrelid LEFT OUTER JOIN pg_attrdef AS adef ON a.attrelid = adef.adrelid AND a.attnum = adef.adnum WHERE c.relkind = 'r' AND a.attnum > 0 ORDER BY a.attnum ) --CONSTRAINT LIST UNION (SELECT n.nspname AS schemaname ,c.relname AS tablename ,200000000 + CAST(con.oid AS INT) AS seq ,'\t,' + pg_get_constraintdef(con.oid) AS ddl FROM pg_constraint AS con INNER JOIN pg_class AS c ON c.relnamespace = con.connamespace AND c.oid = con.conrelid INNER JOIN pg_namespace AS n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND pg_get_constraintdef(con.oid) NOT LIKE 'FOREIGN KEY%' ORDER BY seq) --CLOSE PAREN COLUMN LIST UNION SELECT n.nspname AS schemaname, c.relname AS tablename, 299999999 AS seq, ')' AS ddl FROM pg_namespace AS n INNER JOIN pg_class AS c ON n.oid = c.relnamespace WHERE c.relkind = 'r' --BACKUP UNION SELECT n.nspname AS schemaname ,c.relname AS tablename ,300000000 AS seq ,'BACKUP NO' as ddl
FROM pg_namespace AS n
INNER JOIN pg_class AS c ON n.oid = c.relnamespace INNER JOIN (SELECT SPLIT_PART(key,'_',5) id FROM pg_conf WHERE key LIKE 'pg_class_backup_%' AND SPLIT_PART(key,'_',4) = (SELECT oid FROM pg_database WHERE datname = current_database())) t ON t.id=c.oid WHERE c.relkind = 'r' --BACKUP WARNING UNION SELECT n.nspname AS schemaname ,c.relname AS tablename ,1 AS seq ,'--WARNING: This DDL inherited the BACKUP NO property from the source table' as ddl
FROM pg_namespace AS n
INNER JOIN pg_class AS c ON n.oid = c.relnamespace INNER JOIN (SELECT SPLIT_PART(key,'_',5) id FROM pg_conf WHERE key LIKE 'pg_class_backup_%' AND SPLIT_PART(key,'_',4) = (SELECT oid FROM pg_database WHERE datname = current_database())) t ON t.id=c.oid WHERE c.relkind = 'r' --DISTSTYLE UNION SELECT n.nspname AS schemaname ,c.relname AS tablename ,300000001 AS seq ,CASE WHEN c.reldiststyle = 0 THEN 'DISTSTYLE EVEN' WHEN c.reldiststyle = 1 THEN 'DISTSTYLE KEY' WHEN c.reldiststyle = 8 THEN 'DISTSTYLE ALL' ELSE '<<Error - UNKNOWN DISTSTYLE>>' END AS ddl FROM pg_namespace AS n INNER JOIN pg_class AS c ON n.oid = c.relnamespace WHERE c.relkind = 'r' --DISTKEY COLUMNS UNION SELECT n.nspname AS schemaname ,c.relname AS tablename ,400000000 + a.attnum AS seq ,'DISTKEY (' + QUOTE_IDENT(a.attname) + ')' AS ddl FROM pg_namespace AS n INNER JOIN pg_class AS c ON n.oid = c.relnamespace INNER JOIN pg_attribute AS a ON c.oid = a.attrelid WHERE c.relkind = 'r' AND a.attisdistkey IS TRUE AND a.attnum > 0 --SORTKEY COLUMNS UNION select schemaname, tablename, seq, case when min_sort <0 then 'INTERLEAVED SORTKEY (' else 'SORTKEY (' end as ddl
from (SELECT
n.nspname AS schemaname ,c.relname AS tablename ,499999999 AS seq ,min(attsortkeyord) min_sort FROM pg_namespace AS n INNER JOIN pg_class AS c ON n.oid = c.relnamespace INNER JOIN pg_attribute AS a ON c.oid = a.attrelid WHERE c.relkind = 'r' AND abs(a.attsortkeyord) > 0 AND a.attnum > 0 group by 1,2,3 ) UNION (SELECT n.nspname AS schemaname ,c.relname AS tablename ,500000000 + abs(a.attsortkeyord) AS seq ,CASE WHEN abs(a.attsortkeyord) = 1 THEN '\t' + QUOTE_IDENT(a.attname) ELSE '\t, ' + QUOTE_IDENT(a.attname) END AS ddl FROM pg_namespace AS n INNER JOIN pg_class AS c ON n.oid = c.relnamespace INNER JOIN pg_attribute AS a ON c.oid = a.attrelid WHERE c.relkind = 'r' AND abs(a.attsortkeyord) > 0 AND a.attnum > 0 ORDER BY abs(a.attsortkeyord)) UNION SELECT n.nspname AS schemaname ,c.relname AS tablename ,599999999 AS seq ,'\t)' AS ddl FROM pg_namespace AS n INNER JOIN pg_class AS c ON n.oid = c.relnamespace INNER JOIN pg_attribute AS a ON c.oid = a.attrelid WHERE c.relkind = 'r' AND abs(a.attsortkeyord) > 0 AND a.attnum > 0 --END SEMICOLON UNION SELECT n.nspname AS schemaname, c.relname AS tablename, 600000000 AS seq, ';' AS ddl FROM pg_namespace AS n INNER JOIN pg_class AS c ON n.oid = c.relnamespace WHERE c.relkind = 'r' ) UNION ( SELECT 'zzzzzzzz' || n.nspname AS schemaname, 'zzzzzzzz' || c.relname AS tablename, 700000000 + CAST(con.oid AS INT) AS seq, 'ALTER TABLE ' + QUOTE_IDENT(n.nspname) + '.' + QUOTE_IDENT(c.relname) + ' ADD ' + pg_get_constraintdef(con.oid)::VARCHAR(1024) + ';' AS ddl FROM pg_constraint AS con INNER JOIN pg_class AS c ON c.relnamespace = con.connamespace AND c.oid = con.conrelid INNER JOIN pg_namespace AS n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND con.contype = 'f' ORDER BY seq ) ORDER BY schemaname, tablename, seq )
;