关于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’);