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