PostgreSQL 通过SQL获取建表语句实现 show create table

有scheme参数

创建函数

CREATE OR REPLACE FUNCTION show_create_table(
    in_schema_name varchar,
    in_table_name varchar
)
    RETURNS text
    LANGUAGE plpgsql VOLATILE
AS
$$
DECLARE
    -- the ddl we're building
    v_table_ddl text;

    -- data about the target table
    v_table_oid int;

    v_table_type char;
    v_partition_key varchar;
    v_table_comment varchar;

    -- records for looping
    v_column_record record;
    v_constraint_record record;
    v_index_record record;
    v_column_comment_record record;
    v_index_comment_record record;
    v_constraint_comment_record record;
BEGIN
    -- grab the oid of the table; https://www.postgresql.org/docs/8.3/catalog-pg-class.html
    SELECT c.oid, c.relkind INTO v_table_oid, v_table_type
    FROM pg_catalog.pg_class c
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind in ('r', 'p')
      AND c.relname = in_table_name -- the table name
      AND n.nspname = in_schema_name; -- the schema

    -- throw an error if table was not found
    IF (v_table_oid IS NULL) THEN
        RAISE EXCEPTION 'table does not exist';
    END IF;

    -- start the create definition
    v_table_ddl := 'CREATE TABLE "' || in_table_name || '" (' || E'\n';

    -- define all of the columns in the table; https://stackoverflow.com/a/8153081/3068233
    FOR v_column_record IN
        SELECT
            c.column_name,
            c.data_type,
            c.character_maximum_length,
            c.is_nullable,
            c.column_default
        FROM information_schema.columns c
        WHERE (table_schema, table_name) = (in_schema_name, in_table_name)
        ORDER BY ordinal_position
        LOOP
            v_table_ddl := v_table_ddl || '  ' -- note: two char spacer to start, to indent the column
                               || '"' || v_column_record.column_name || '" '
                               || v_column_record.data_type || CASE WHEN v_column_record.character_maximum_length IS NOT NULL THEN ('(' || v_column_record.character_maximum_length || ')') ELSE '' END || ' '
                               || CASE WHEN v_column_record.is_nullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
                               || CASE WHEN v_column_record.column_default IS NOT null THEN (' DEFAULT ' || v_column_record.column_default) ELSE '' END
                               || ',' || E'\n';
        END LOOP;

    -- define all the constraints in the; https://www.postgresql.org/docs/9.1/catalog-pg-constraint.html && https://dba.stackexchange.com/a/214877/75296
    FOR v_constraint_record IN
        SELECT
            con.conname as constraint_name,
            con.contype as constraint_type,
            CASE
                WHEN con.contype = 'p' THEN 1 -- primary key constraint
                WHEN con.contype = 'u' THEN 2 -- unique constraint
                WHEN con.contype = 'f' THEN 3 -- foreign key constraint
                WHEN con.contype = 'c' THEN 4
                ELSE 5
                END as type_rank,
            pg_get_constraintdef(con.oid) as constraint_definition
        FROM pg_catalog.pg_constraint con
                 JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
                 JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
        WHERE nsp.nspname = in_schema_name
          AND rel.relname = in_table_name
        ORDER BY type_rank
        LOOP
            IF v_constraint_record.constraint_type = 'p' THEN
                v_table_ddl := v_table_ddl || '  '
                                   || v_constraint_record.constraint_definition
                                   || ',' || E'\n';
            ELSE
                v_table_ddl := v_table_ddl || '  ' -- note: two char spacer to start, to indent the column
                                   || 'CONSTRAINT' || ' '
                                   || '"' || v_constraint_record.constraint_name || '" '
                                   || v_constraint_record.constraint_definition
                                   || ',' || E'\n';
            END IF;
        END LOOP;

    -- drop the last comma before ending the create statement
    v_table_ddl = substr(v_table_ddl, 0, length(v_table_ddl) - 1) || E'\n';

    -- end the create definition
    v_table_ddl := v_table_ddl || ')';

    IF v_table_type = 'p' THEN
        SELECT pg_get_partkeydef(v_table_oid) INTO v_partition_key;
        IF v_partition_key IS NOT NULL THEN
            v_table_ddl := v_table_ddl || ' PARTITION BY ' || v_partition_key;
        END IF;
    END IF;

    v_table_ddl := v_table_ddl || ';' || E'\n';

    -- suffix create statement with all of the indexes on the table
    FOR v_index_record IN
        SELECT regexp_replace(indexdef, ' "?' || schemaname || '"?\.', ' ') AS indexdef
        FROM pg_catalog.pg_indexes
        WHERE (schemaname, tablename) = (in_schema_name, in_table_name)
          AND indexname NOT IN (
            select con.conname
            FROM pg_catalog.pg_constraint con
                     JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
                     JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
            WHERE nsp.nspname = in_schema_name
              AND rel.relname = in_table_name
        )
        LOOP
            v_table_ddl := v_table_ddl
                               || v_index_record.indexdef
                               || ';' || E'\n';
        END LOOP;

    -- comment on table
    SELECT description INTO v_table_comment
    FROM pg_catalog.pg_description
    WHERE objoid = v_table_oid AND objsubid = 0;

    IF v_table_comment IS NOT NULL THEN
        v_table_ddl := v_table_ddl || 'COMMENT ON TABLE "' || in_table_name || '" IS ''' || replace(v_table_comment, '''', '''''') || ''';' || E'\n';
    END IF;

    -- comment on column
    FOR v_column_comment_record IN
        SELECT col.column_name, d.description
        FROM information_schema.columns col
                 JOIN pg_catalog.pg_class c ON c.relname = col.table_name
                 JOIN pg_catalog.pg_namespace nsp ON nsp.oid = c.relnamespace AND col.table_schema = nsp.nspname
                 JOIN pg_catalog.pg_description d ON d.objoid = c.oid AND d.objsubid = col.ordinal_position
        WHERE c.oid = v_table_oid
        ORDER BY col.ordinal_position
        LOOP
            v_table_ddl := v_table_ddl || 'COMMENT ON COLUMN "' || in_table_name || '"."'
                               || v_column_comment_record.column_name || '" IS '''
                               || replace(v_column_comment_record.description, '''', '''''') || ''';' || E'\n';
        END LOOP;

    -- comment on index
    FOR v_index_comment_record IN
        SELECT c.relname, d.description
        FROM pg_catalog.pg_index idx
                 JOIN pg_catalog.pg_class c ON idx.indexrelid = c.oid
                 JOIN pg_catalog.pg_description d ON idx.indexrelid = d.objoid
        WHERE idx.indrelid = v_table_oid
        LOOP
            v_table_ddl := v_table_ddl || 'COMMENT ON INDEX "'
                               || v_index_comment_record.relname || '" IS '''
                               || replace(v_index_comment_record.description, '''', '''''') || ''';' || E'\n';
        END LOOP;

    -- comment on constraint
    FOR v_constraint_comment_record IN
        SELECT
            con.conname,
            pg_description.description
        FROM pg_catalog.pg_constraint con
                 JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
                 JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
                 JOIN pg_catalog.pg_description ON pg_description.objoid = con.oid
        WHERE rel.oid = v_table_oid
        LOOP
            v_table_ddl := v_table_ddl || 'COMMENT ON CONSTRAINT "'
                               || v_constraint_comment_record.conname || '" ON "' || in_table_name || '" IS '''
                               || replace(v_constraint_comment_record.description, '''', '''''') || ''';' || E'\n';
        END LOOP;

    -- return the ddl
    RETURN v_table_ddl;
END
$$;

使用

select show_create_table('public', 't1');

输出

CREATE TABLE "t1" (
  "id" integer NOT NULL,
  "name" character varying(255) NULL,
  "create_time" timestamp without time zone NOT NULL DEFAULT now(),
  "user_id" integer NULL,
  CONSTRAINT "t1_pk" UNIQUE (name),
  CONSTRAINT "t1_tuser_id_fk" FOREIGN KEY (user_id) REFERENCES t_user(id)
);
CREATE INDEX t1_create_time_index ON t1 USING btree (create_time);
COMMENT ON TABLE "t1" IS '测试表';
COMMENT ON COLUMN "t1"."name" IS '名称';
COMMENT ON INDEX "t1_create_time_index" IS '创建时间索引';
COMMENT ON CONSTRAINT "t1_t_user_id_fk" ON "t1" IS '用户外键';

无scheme参数，只查当前连接的scheme

创建函数

CREATE OR REPLACE FUNCTION show_create_table(
    in_table_name varchar
)
    RETURNS text
    LANGUAGE plpgsql VOLATILE
AS
$$
DECLARE
    -- the ddl we're building
    v_table_ddl text;

    -- data about the target table
    v_table_oid int;

    v_table_type char;
    v_partition_key varchar;
    v_namespace varchar;
    v_table_comment varchar;

    -- records for looping
    v_column_record record;
    v_constraint_record record;
    v_index_record record;
    v_column_comment_record record;
    v_index_comment_record record;
    v_constraint_comment_record record;
BEGIN
    -- grab the oid of the table; https://www.postgresql.org/docs/8.3/catalog-pg-class.html
    SELECT c.oid, c.relkind, n.nspname INTO v_table_oid, v_table_type, v_namespace
    FROM pg_catalog.pg_class c
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind in ('r', 'p')
      AND c.relname = in_table_name -- the table name
      AND pg_catalog.pg_table_is_visible(c.oid); -- the schema

    -- throw an error if table was not found
    IF (v_table_oid IS NULL) THEN
        RAISE EXCEPTION 'table does not exist';
    END IF;

    -- start the create definition
    v_table_ddl := 'CREATE TABLE "' || in_table_name || '" (' || E'\n';

    -- define all of the columns in the table; https://stackoverflow.com/a/8153081/3068233
    FOR v_column_record IN
        SELECT
            c.column_name,
            c.data_type,
            c.character_maximum_length,
            c.is_nullable,
            c.column_default
        FROM information_schema.columns c
        WHERE table_name = in_table_name and table_schema = v_namespace
        ORDER BY ordinal_position
        LOOP
            v_table_ddl := v_table_ddl || '  ' -- note: two char spacer to start, to indent the column
                               || '"' || v_column_record.column_name || '" '
                               || v_column_record.data_type || CASE WHEN v_column_record.character_maximum_length IS NOT NULL THEN ('(' || v_column_record.character_maximum_length || ')') ELSE '' END || ' '
                               || CASE WHEN v_column_record.is_nullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
                               || CASE WHEN v_column_record.column_default IS NOT null THEN (' DEFAULT ' || v_column_record.column_default) ELSE '' END
                               || ',' || E'\n';
        END LOOP;

    -- define all the constraints in the; https://www.postgresql.org/docs/9.1/catalog-pg-constraint.html && https://dba.stackexchange.com/a/214877/75296
    FOR v_constraint_record IN
        SELECT
            con.conname as constraint_name,
            con.contype as constraint_type,
            CASE
                WHEN con.contype = 'p' THEN 1 -- primary key constraint
                WHEN con.contype = 'u' THEN 2 -- unique constraint
                WHEN con.contype = 'f' THEN 3 -- foreign key constraint
                WHEN con.contype = 'c' THEN 4
                ELSE 5
                END as type_rank,
            pg_get_constraintdef(con.oid) as constraint_definition
        FROM pg_catalog.pg_constraint con
                 JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
                 JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
        WHERE rel.relname = in_table_name
          AND pg_catalog.pg_table_is_visible(rel.oid)
        ORDER BY type_rank
        LOOP
            IF v_constraint_record.constraint_type = 'p' THEN
                v_table_ddl := v_table_ddl || '  '
                                   || v_constraint_record.constraint_definition
                                   || ',' || E'\n';
            ELSE
                v_table_ddl := v_table_ddl || '  ' -- note: two char spacer to start, to indent the column
                                   || 'CONSTRAINT' || ' '
                                   || '"' || v_constraint_record.constraint_name || '" '
                                   || v_constraint_record.constraint_definition
                                   || ',' || E'\n';
            END IF;
        END LOOP;

    -- drop the last comma before ending the create statement
    v_table_ddl = substr(v_table_ddl, 0, length(v_table_ddl) - 1) || E'\n';

    -- end the create definition
    v_table_ddl := v_table_ddl || ')';

    IF v_table_type = 'p' THEN
        SELECT pg_get_partkeydef(v_table_oid) INTO v_partition_key;
        IF v_partition_key IS NOT NULL THEN
            v_table_ddl := v_table_ddl || ' PARTITION BY ' || v_partition_key;
        END IF;
    END IF;

    v_table_ddl := v_table_ddl || ';' || E'\n';

    -- suffix create statement with all of the indexes on the table
    FOR v_index_record IN
        SELECT regexp_replace(idx.indexdef, ' "?' || idx.schemaname || '"?\.', ' ') AS indexdef
        FROM pg_indexes idx
                 JOIN (
            SELECT ns.nspname, cls.relname
            FROM pg_catalog.pg_class cls
                     LEFT JOIN pg_catalog.pg_namespace ns ON ns.oid = cls.relnamespace
            WHERE pg_catalog.pg_table_is_visible(cls.oid)
        ) t ON idx.schemaname = t.nspname AND idx.tablename = t.relname
        WHERE idx.tablename = in_table_name
          AND idx.indexname NOT IN (
            select con.conname
            FROM pg_catalog.pg_constraint con
                     JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
                     JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
            WHERE rel.relname = in_table_name
              AND pg_catalog.pg_table_is_visible(rel.oid)
        )
        LOOP
            v_table_ddl := v_table_ddl
                               || v_index_record.indexdef
                               || ';' || E'\n';
        END LOOP;

    -- comment on table
    SELECT description INTO v_table_comment
    FROM pg_catalog.pg_description
    WHERE objoid = v_table_oid AND objsubid = 0;

    IF v_table_comment IS NOT NULL THEN
        v_table_ddl := v_table_ddl || 'COMMENT ON TABLE "' || in_table_name || '" IS ''' || replace(v_table_comment, '''', '''''') || ''';' || E'\n';
    END IF;

    -- comment on column
    FOR v_column_comment_record IN
        SELECT col.column_name, d.description
        FROM information_schema.columns col
                 JOIN pg_catalog.pg_class c ON c.relname = col.table_name
                 JOIN pg_catalog.pg_namespace nsp ON nsp.oid = c.relnamespace AND col.table_schema = nsp.nspname
                 JOIN pg_catalog.pg_description d ON d.objoid = c.oid AND d.objsubid = col.ordinal_position
        WHERE c.oid = v_table_oid
        ORDER BY col.ordinal_position
        LOOP
            v_table_ddl := v_table_ddl || 'COMMENT ON COLUMN "' || in_table_name || '"."'
                               || v_column_comment_record.column_name || '" IS '''
                               || replace(v_column_comment_record.description, '''', '''''') || ''';' || E'\n';
        END LOOP;

    -- comment on index
    FOR v_index_comment_record IN
        SELECT c.relname, d.description
        FROM pg_catalog.pg_index idx
                 JOIN pg_catalog.pg_class c ON idx.indexrelid = c.oid
                 JOIN pg_catalog.pg_description d ON idx.indexrelid = d.objoid
        WHERE idx.indrelid = v_table_oid
        LOOP
            v_table_ddl := v_table_ddl || 'COMMENT ON INDEX "'
                               || v_index_comment_record.relname || '" IS '''
                               || replace(v_index_comment_record.description, '''', '''''') || ''';' || E'\n';
        END LOOP;

    -- comment on constraint
    FOR v_constraint_comment_record IN
        SELECT
            con.conname,
            pg_description.description
        FROM pg_catalog.pg_constraint con
                 JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
                 JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
                 JOIN pg_catalog.pg_description ON pg_description.objoid = con.oid
        WHERE rel.oid = v_table_oid
        LOOP
            v_table_ddl := v_table_ddl || 'COMMENT ON CONSTRAINT "'
                               || v_constraint_comment_record.conname || '" ON "' || in_table_name || '" IS '''
                               || replace(v_constraint_comment_record.description, '''', '''''') || ''';' || E'\n';
        END LOOP;

    -- return the ddl
    RETURN v_table_ddl;
END
$$;

使用

select show_create_table('t1');

输出

CREATE TABLE "t1" (
  "id" integer NOT NULL,
  "name" character varying(255) NULL,
  "create_time" timestamp without time zone NOT NULL DEFAULT now(),
  "user_id" integer NULL,
  CONSTRAINT "t1_pk" UNIQUE (name),
  CONSTRAINT "t1_t_user_id_fk" FOREIGN KEY (user_id) REFERENCES t_user(id)
);
CREATE INDEX t1_create_time_index ON t1 USING btree (create_time);
COMMENT ON TABLE "t1" IS '测试表';
COMMENT ON COLUMN "t1"."name" IS '名称';
COMMENT ON INDEX "t1_create_time_index" IS '创建时间索引';
COMMENT ON CONSTRAINT "t1_t_user_id_fk" ON "t1" IS '用户外键';

参考

• How to generate the "create table" sql statement for an existing table in postgreSQL - Stack Overflow

本栏目推荐文章
• 用于PostgreSQL索引维护的有用查询
• 4- if 流程语句和案例
• element表格table点击行，改变当前行的背景颜色
• 【ubantu22.10】安装部署timescaledbv2.13.0及postgresql v14.10
• Ubuntu 安装Postgresql与PostGIS
• 2024-01-13 antd的tabel组件业务问题之勾选了table中的一项，然后弹出弹窗，接着关闭弹窗，刷新table，但是table选中的一项还是显示被勾选中的状态 ==》你没有改变所选中的数据（selectedRowKeys）
• mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; ERROR 1410 (42000): You are not allowed to create a user with GRANT
• Oracle 19c 升级错误【verify_queryable_inventory returned ORA-20001: Latest xml inventory is not loaded into table】
• el-table 某些行不可勾选
• PostgreSQL从小白到高手教程 - 第41讲：postgres表空间备份与恢复

语句 PostgreSQL create table show语句postgresql create table show-summary element-ui el-table element ltgtt_create_temp_table 语句 条件v-show vue3 create table mysql like status table信息show postgresql containers creating actions procedure create mysql table 索引create alter table ltgtt_create_temp_table逻辑create ltgtt