AWS Redshift utilsにビューのDDLを自動生成するSQLが公開されているが、 COMMENT文も欲しかったので過去に自作したものを公開。 ただ、SQL文に改行やインデントがはいらず、なんか微妙な感じ。
WITH v_generate_view_ddl AS ( SELECT derived_table4.schemaname AS schemaname, derived_table4.tablename AS tablename, derived_table4.seq, derived_table4.ddl FROM (SELECT derived_table3.schemaname, derived_table3.tablename, derived_table3.seq, derived_table3.ddl FROM ((((((SELECT n.nspname AS schemaname, c.relname AS tablename, 0 AS seq, ('--DROP VIEW "'::VARCHAR(65535) + n.nspname::VARCHAR(65535) + '"."'::VARCHAR(65535) + c.relname::VARCHAR(65535) + '";'::VARCHAR(65535))::VARCHAR(65535) AS ddl FROM pg_namespace n JOIN pg_class c ON n.oid = c.relnamespace WHERE c.relkind = 'v' UNION SELECT n.nspname AS schemaname, c.relname AS tablename, 2 AS seq, ('CREATE VIEW "'::VARCHAR(65535) + n.nspname::VARCHAR(65535) + '"."'::VARCHAR(65535) + c.relname::VARCHAR(65535) + '"'::VARCHAR(65535))::VARCHAR(65535) AS ddl FROM pg_namespace n JOIN pg_class c ON n.oid = c.relnamespace WHERE c.relkind = 'v') UNION SELECT n.nspname AS schemaname, c.relname AS tablename, 5 AS seq, '('::VARCHAR(65535) AS ddl FROM pg_namespace n JOIN pg_class c ON n.oid = c.relnamespace WHERE c.relkind = 'v') UNION SELECT derived_table1.schemaname, derived_table1.tablename, derived_table1.seq, ('\011'::VARCHAR(65535) + derived_table1.col_delim + derived_table1.col_name + ' '::VARCHAR(65535) /* + derived_table1.col_datatype + ' '::varchar(65535) + derived_table1.col_nullable + ' '::varchar(65535) + derived_table1.col_default + ' '::varchar(65535) + derived_table1.col_encoding*/)::VARCHAR(65535) AS ddl FROM (SELECT n.nspname AS schemaname, c.relname AS tablename, 100000000 + a.attnum AS seq, CASE WHEN a.attnum > 1 THEN ','::VARCHAR(65535) ELSE ''::VARCHAR(65535) END AS col_delim, '"'::VARCHAR(65535) + a.attname::VARCHAR(65535) + '"'::VARCHAR(65535) AS col_name FROM pg_namespace n JOIN pg_class c ON n.oid = c.relnamespace JOIN pg_attribute a ON c.oid = a.attrelid LEFT JOIN pg_attrdef adef ON a.attrelid = adef.adrelid AND a.attnum = adef.adnum WHERE c.relkind = 'v' AND a.attnum > 0 ORDER BY a.attnum) derived_table1) UNION SELECT n.nspname AS schemaname, c.relname AS tablename, 299999999 AS seq, ')'::VARCHAR(65535) AS ddl FROM pg_namespace n JOIN pg_class c ON n.oid = c.relnamespace WHERE c.relkind = 'v') UNION SELECT n.nspname AS schemaname, c.relname AS tablename, 300000000 AS seq, 'AS'::VARCHAR(65535) AS ddl FROM pg_namespace n JOIN pg_class c ON n.oid = c.relnamespace WHERE c.relkind = 'v') UNION SELECT n.schemaname AS schemaname, n.viewname AS tablename, 500000000 AS seq, definition::VARCHAR(65535) AS ddl FROM pg_views n --TABLE COMMENT UNION SELECT schemaname, tablename, seq, 'COMMENT ON VIEW ' || schemaname || '.' || tablename || ' IS ''' || description || ''';' AS ddl FROM (SELECT n.nspname AS schemaname, c.relname AS tablename, 800000000 AS seq, d.description AS description 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 INNER JOIN pg_description d ON a.attrelid = d.objoid AND d.objsubid = 0 WHERE c.relkind = 'v' AND a.attnum > 0) -- COLUMN COMMENT UNION SELECT schemaname, tablename, seq, 'COMMENT ON COLUMN ' || schemaname || '.' || tablename || '.' || attrname || ' IS ''' || description || ''';' AS ddl FROM (SELECT n.nspname AS schemaname, c.relname AS tablename, 800000000 + a.attnum AS seq, a.attname AS attrname, d.description AS description 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 INNER JOIN pg_description d ON a.attrelid = d.objoid AND a.attnum = d.objsubid WHERE c.relkind = 'v' AND a.attnum > 0)) derived_table3 ORDER BY 1, 2, 3) derived_table4 ) SELECT * FROM v_generate_view_ddl WHERE schemaname NOT IN ('pg_catalog','information_schema','pg_internal','public') ORDER BY schemaname, tablename, seq
下記を参考に、ある程度改行付きのものを作成した。でもなんか不格好。
amazon-redshift-utils/v_generate_view_ddl.sql at master · awslabs/amazon-redshift-utils · GitHub
WITH v_generate_view_ddl AS ( SELECT derived_table4.schemaname AS schemaname, derived_table4.tablename AS tablename, derived_table4.seq, derived_table4.ddl FROM (SELECT derived_table3.schemaname, derived_table3.tablename, derived_table3.seq, derived_table3.ddl FROM ( ( ( SELECT n.nspname AS schemaname, c.relname AS tablename, 0 AS seq, '--DROP VIEW "' + n.nspname + '"."' + c.relname + '";' AS ddl FROM pg_namespace n JOIN pg_class c ON n.oid = c.relnamespace WHERE c.relkind = 'v' ) UNION SELECT n.nspname AS schemaname, c.relname AS viewname, 200000000 AS seq, CASE WHEN c.relnatts > 0 THEN 'CREATE OR REPLACE VIEW ' + QUOTE_IDENT(n.nspname) + '.' + QUOTE_IDENT(c.relname) + ' AS\n' +COALESCE(pg_get_viewdef (c.oid,TRUE),'') ELSE COALESCE(pg_get_viewdef (c.oid,TRUE),'') END AS ddl FROM pg_catalog.pg_class AS c JOIN pg_catalog.pg_namespace AS n ON c.relnamespace = n.oid WHERE relkind = 'v' ) --TABLE COMMENT UNION SELECT schemaname, tablename, seq, 'COMMENT ON VIEW ' || schemaname || '.' || tablename || ' IS ''' || description || ''';' AS ddl FROM ( SELECT n.nspname AS schemaname, c.relname AS tablename, 800000000 AS seq, d.description AS description 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 INNER JOIN pg_description d ON a.attrelid = d.objoid AND d.objsubid = 0 WHERE c.relkind = 'v' AND a.attnum > 0 ) -- COLUMN COMMENT UNION SELECT schemaname, tablename, seq, 'COMMENT ON COLUMN ' || schemaname || '.' || tablename || '.' || attrname || ' IS ''' || description || ''';' AS ddl FROM ( SELECT n.nspname AS schemaname, c.relname AS tablename, 800000000 + a.attnum AS seq, a.attname AS attrname, d.description AS description 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 INNER JOIN pg_description d ON a.attrelid = d.objoid AND a.attnum = d.objsubid WHERE c.relkind = 'v' AND a.attnum > 0 ) ) derived_table3 ORDER BY 1, 2, 3 ) derived_table4 ) SELECT * FROM v_generate_view_ddl WHERE schemaname NOT IN ('pg_catalog','information_schema','pg_internal','public') ORDER BY schemaname, tablename, seq
