PostgreSQL獲取建表語句儲存過程

PG版本：

 PostgreSQL 11.11

儲存過程：

create or replace function get_tab_ddl(tab_name varchar)returns text as $$declare

    --定義變數

    tab_ddl text;

    curs refcursor;

    tmp_col record;

    tab_info record;begin 

    --獲取表的pid、schema資訊

    open curs for SELECT c.oid,n.nspname,c.relname FROM pg_catalog.pg_class c    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace    WHERE c.relname ~ ('^('||tab_name||')$')AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2,3;

    fetch curs into tmp_col;

    --判斷是否存在該表

    if tmp_col.oid is null then

        return 'Table "'||tab_name||'" was not queried';

    end if;

    --如表存在，獲取表的列資訊

    FOR tab_info IN

        SELECT

            a.attname as col_name,

            pg_catalog.format_type(a.atttypid, a.atttypmod) as col_type,

            CASE WHEN

                (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)

                FROM pg_catalog.pg_attrdef d                WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN

                'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)

                              FROM pg_catalog.pg_attrdef d                              WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)

            ELSE

                ''

            END as col_default_value,

            CASE WHEN a.attnotnull = true THEN

                'NOT NULL'

            ELSE

                'NULL'

            END as col_not_null,

            a.attnum as attnum,

            e.max_attnum as max_attnum        FROM

            pg_catalog.pg_attribute a            INNER JOIN

            (SELECT

                  a.attrelid,

                  max(a.attnum) as max_attnum              FROM pg_catalog.pg_attribute a              WHERE a.attnum > 0

                AND NOT a.attisdropped              GROUP BY a.attrelid) e            ON a.attrelid=e.attrelid        WHERE a.attnum > 0

          AND a.attrelid=tmp_col.oid          AND NOT a.attisdropped        ORDER BY a.attnum    --拼接為ddl語句

    LOOP

        IF tab_info.attnum = 1 THEN

            tab_ddl:='CREATE TABLE '||tmp_col.nspname||'.'||tmp_col.relname||' (';

        ELSE

            tab_ddl:=tab_ddl||',';

        END IF;

        IF tab_info.attnum <= tab_info.max_attnum THEN

            tab_ddl:=tab_ddl||chr(10)||'    '||tab_info.col_name||' '||tab_info.col_type||' '||tab_info.col_default_value||' '||tab_info.col_not_null;

        END IF;

    END LOOP;

      tab_ddl:=tab_ddl||');';

    --輸出結果

    RETURN tab_ddl;end;$$ language plpgsql;

用法：

select get_tab_ddl('table_name');