替换掉ias.tb_bil_statement_553
SELECT array_to_string(ARRAY(
SELECT ret FROM (
(
-- 主表ias.tb_bil_statement_553的结构,包括字段、约束和分区键
SELECT 'CREATE TABLE ias.tb_bil_statement_553 (' || array_to_string(ARRAY(
SELECT SQL FROM (
(
-- 字段信息
SELECT array_to_string(ARRAY(
SELECT A.attname || ' ' || concat_ws('', T.typname, SUBSTRING(format_type(A.atttypid, A.atttypmod) FROM '\\(.*\\)')) || ' ' ||
CASE WHEN A.attnotnull THEN 'NOT NULL' ELSE '' END || ' ' ||
CASE WHEN D.adbin IS NOT NULL THEN ' DEFAULT ' || pg_get_expr(D.adbin, A.attrelid) ELSE '' END
FROM pg_attribute A
LEFT JOIN pg_description b ON A.attrelid = b.objoid AND A.attnum = b.objsubid
LEFT JOIN pg_attrdef D ON A.attrelid = D.adrelid AND A.attnum = D.adnum
JOIN pg_type T ON A.atttypid = T.OID
WHERE A.attrelid = 'ias.tb_bil_statement_553'::REGCLASS::OID AND
A.attnum > 0 AND NOT A.attisdropped
ORDER BY A.attnum
), ',' || CHR(10)) AS SQL, 1 AS seri
)
UNION
(
-- 约束信息
SELECT 'CONSTRAINT ' || conname || ' ' || pg_get_constraintdef(OID) AS SQL, 2 AS seri
FROM pg_constraint
WHERE conrelid = 'ias.tb_bil_statement_553'::REGCLASS::OID
ORDER BY contype DESC
)
) AS T
), ',' || CHR(10)) ||
' ) PARTITION BY ' || pg_get_partkeydef('ias.tb_bil_statement_553'::REGCLASS) AS ret, 1 AS orderby
)
UNION
(
-- 每个子分区表的定义
SELECT array_to_string(ARRAY(
SELECT 'create table ' || c.oid :: pg_catalog.regclass || ' PARTITION OF ias.tb_bil_statement_553 ' || pg_catalog.pg_get_expr(c.relpartbound, c.oid) || '' AS ret
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_inherits i ON c.oid = i.inhrelid
WHERE i.inhparent = 'ias.tb_bil_statement_553'::REGCLASS::OID
ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid :: pg_catalog.regclass :: pg_catalog.text
), ';' || CHR(10)) AS ret, 2 AS orderby
)
UNION
(
-- 注释信息
SELECT array_to_string(ARRAY(
SELECT 'COMMENT ON COLUMN ias.tb_bil_statement_553.' || A.attname || ' IS ''' || b.description || ''''
FROM pg_attribute A
LEFT JOIN pg_description b ON A.attrelid = b.objoid AND A.attnum = b.objsubid
WHERE A.attrelid = 'ias.tb_bil_statement_553'::REGCLASS::OID AND
b.description IS NOT NULL
ORDER BY A.attnum
), ';' || CHR(10)) AS ret, 3 AS orderby
)
UNION
(
-- 索引信息
SELECT array_to_string(ARRAY(
SELECT pg_get_indexdef(indexrelid)
FROM pg_index
WHERE indrelid = 'ias.tb_bil_statement_553'::REGCLASS::OID AND
indisprimary = 'f' AND
indisunique = 'f'
), ';' || CHR(10)) AS ret, 4 AS orderby
)
) AS results
ORDER BY orderby
), ';' || CHR(10) || CHR(13))