Postgres使用数据字典表生成sql输出

SELECT 'CREATE TABLE ' || tablename || ' (' ||
       array_to_string(
           ARRAY(
               SELECT column_name || ' ' || data_type ||
                      CASE WHEN character_maximum_length IS NOT NULL THEN '(' || character_maximum_length || ')' ELSE '' END ||
                      CASE WHEN is_nullable = 'NO' THEN ' NOT NULL' ELSE '' END
               FROM information_schema.columns
               WHERE table_name = tablename
               ORDER BY ordinal_position
           ),
           ', '
       ) || ');'
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY tablename;

SELECT 'CREATE INDEX ' || indexname || ' ON ' || tablename || ' (' ||
       array_to_string(
           ARRAY(
               SELECT column_name
               FROM information_schema.index_columns
               WHERE table_name = tablename AND index_name = indexname
               ORDER BY ordinal_position
           ),
           ', '
       ) || ');'
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

SELECT 'ALTER TABLE ' || tablename || ' ADD CONSTRAINT ' || constraint_name ||
       ' ' || constraint_type || ' (' ||
       array_to_string(
           ARRAY(
               SELECT column_name
               FROM information_schema.constraint_column_usage
               WHERE table_name = tablename AND constraint_name = constraintname
               ORDER BY ordinal_position
           ),
           ', '
       ) || ');'
FROM information_schema.table_constraints
WHERE constraint_type = 'FOREIGN KEY' AND table_schema = 'public'
ORDER BY tablename, constraintname;