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