首页 > 数据库 >pg 如何生成创建表sql语句?

pg 如何生成创建表sql语句?

时间:2023-04-18 10:38:06浏览次数:48  
标签:语句 name column attnum catalog pg sql table


oracle有dbms_metadata.get_ddl;sqlserver可以直接右键;mysql有show create table

但pg好像没有直接的方法,网上找到了几种自己创建函数的方法,收集一下。

法一:

支持生成包含:字段(支持数组类型字段)、约束、索引(支持唯一索引、全类型索引)在内的建表语句。

CREATE OR REPLACE FUNCTION "public"."findattname"("dbinstancename" varchar, "namespace" varchar, "tablename" varchar, "ctype" varchar)
  RETURNS "pg_catalog"."varchar" AS $BODY$
 
declare
tt oid ;
aname character varying default '';
 
begin
       tt := oid from pg_class where relname= tablename 
    and relnamespace =(select oid from pg_namespace  where nspname=namespace and nspowner=(select datdba from pg_database where datname=dbinstancename) ) ;      
       aname:=  array_to_string(
        array(
               select a.attname  from pg_attribute  a 
                where a.attrelid=tt and  a.attnum   in (        
                select unnest(conkey) from pg_constraint c where contype=ctype 
                and conrelid=tt  and array_to_string(conkey,',') is not null  
            ) 
        ),',')
    ;
    
    return aname;
end 
    
    
    $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

法二:

CREATE OR REPLACE FUNCTION generate_create_table_statement(p_table_name varchar)
  RETURNS text AS
$BODY$
DECLARE
    v_table_ddl   text;
    column_record record;
BEGIN
    FOR column_record IN 
        SELECT 
            b.nspname as schema_name,
            b.relname as table_name,
            a.attname as column_name,
            pg_catalog.format_type(a.atttypid, a.atttypmod) as column_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 column_default_value,
            CASE WHEN a.attnotnull = true THEN 
                'NOT NULL'
            ELSE
                'NULL'
            END as column_not_null,
            a.attnum as attnum,
            e.max_attnum as max_attnum
        FROM 
            pg_catalog.pg_attribute a
            INNER JOIN 
             (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 ~ ('^('||p_table_name||')$')
                AND pg_catalog.pg_table_is_visible(c.oid)
              ORDER BY 2, 3) b
            ON a.attrelid = b.oid
            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 NOT a.attisdropped
        ORDER BY a.attnum
    LOOP
        IF column_record.attnum = 1 THEN
            v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' (';
        ELSE
            v_table_ddl:=v_table_ddl||',';
        END IF;

        IF column_record.attnum <= column_record.max_attnum THEN
            v_table_ddl:=v_table_ddl||chr(10)||
                     '    '||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null;
        END IF;
    END LOOP;

    v_table_ddl:=v_table_ddl||');';
    RETURN v_table_ddl;
END;
$BODY$
  LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;

下面是函数的用法:

SELECT generate_create_table_statement('tablename');

删除,DROP语句:

DROP FUNCTION generate_create_table_statement(p_table_name varchar);

法三:pg_dump表结构

pg_dump -t 'aschema.atable' --schema-only database-name

参考

如何在postgreSQL中为现有表生成"create table“sql语句 - 问答 - 腾讯云开发者社区-腾讯云


标签:语句,name,column,attnum,catalog,pg,sql,table
From: https://blog.51cto.com/u_13631369/6202542

相关文章

  • pg 10 主要参数配置整理
    一、查看参数设置show参数名select*frompg_settingswherename='参数名';查看 postgresql.conf文件  二、参数是否可修改pg中主要可优化参数都位于postgresql.conf文件中,其中参数是否可修改可通过pg_settings的context字段查询。postgres=#selectdistinctcontextfrom......
  • pg 物理架构
    架构图各部分用途......
  • PLSQL 多个连接
    PLSQL多个连接文件地址D:\app\think\product\11.2.0\instantclient_11_2\network\admin\tnsnames.ora#tnsnames.oraNetworkConfigurationFile:D:\app\think\product\11.2.0\client_1\NETWORK\ADMIN\tnsnames.ora#GeneratedbyOracleconfigurationtools.......
  • pg事务篇(一)—— 事务与多版本并发控制MVCC
    一、MVCC常用实现方法一般MVCC有2种实现方法:写新数据时,把旧数据快照存入其他位置(如oracle的回滚段、sqlserver的tempdb)。当读数据时,读的是快照的旧数据。写新数据时,旧数据不删除,直接插入新数据。PostgreSQL就是使用的这种实现方法。1.PostgreSQL的MVCC实现方式优缺点优点无论事务......
  • oracle、达梦数据库、MySQL数据创建表与字段注释
    /**1.oracle注释*//*表本身注释*/commentontable表名is'注释信息';/*字段注释*/commentoncolumn表名.字段名is'注释信息';/*实例如下:*/commentontableUSERis'用户表';commentoncolumnUSER.IDis'主键ID';/**2.MySQL注释*//*表本身注释*/altertable表名co......
  • 测试环境治理之MYSQL索引优化篇
    作者:京东物流 李光新1治理背景测试环境这个话题对于开发和测试同学一定不陌生,大家几乎每天都会接触。但是说到对测试环境的印象,却鲜有好评:•环境不稳定,测试五分钟,排查两小时•基础建设不全,导致验证不充分,遗漏缺陷•多人共用,节点堵塞这些问题在行业内其实屡见不鲜,针对测试......
  • KB5024396 - SQL Server 2022 的累积更新 3
    发布日期: 2023年4月13日版本: 16.0.4025.1摘要本文介绍适用于MicrosoftSQLServer2022的累积更新包3(CU3)。此更新包含SQLServer2022累积更新2发布后发布的9个修补程序,并更新以下版本中的组件:SQLServer-产品版本:16.0.4025.1,文件版本:2022.160.4025.......
  • Oracle 单进程可用PGA为4G限制导致的ORA-4030报错
    一、问题背景收到开发反馈,系统报表运行过程中报错,一看发现是ORA-4030,内存的问题查看alert日志,发现期间有大量ORA-4030报错,并且主要是pga相关的打开trace文件,可以看到报错进程使用内存接近4G但是查看pga参数设置,发现设置的上限是20G,完全没到,并且期间总的PGA使用率也不高 二、报错......
  • 应用连MySQL 报错ERROR 1129 Host is blocked because of many connection errors
    开发反馈应用连MySQL报错 createconnectionSQLException,url:连接串,errorCode1129。搜索1129报错,报错内容为:Hostisblockedbecauseofmanyconnectionerrors一、报错原因同一个ip在短时间内产生太多中断的数据库连接(超过mysql数据库max_connection_errors设置),导......
  • Windows系统下,HHDBCS辅助Mysql安装
    通常来说,安装好Mysql后,使用HHDBCS和本机IP,就可以直接连入数据库。但有时候在安装成功的情况下,却连接不上Mysql。此时可以点击测试,会弹出提示框:由提示可知,被Mysql拒绝访问了。我们可以通过CMD来解决这个问题。使用管理员模式进入CMD;在登入mysql后,更改“mysql”数据库里的“......