关于node.js: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 www.cungun.coma.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');