Re: Get the table creation DDL - Mailing list pgsql-general

I understand.
The Problem is that I need to put this inside the C/ODBC interface for
my project.

I'm sure it is not a problem when people are working out of psql or
writing some scripts,
but for me it is painful to go and try to recreate it.

Now, I'm not sure if this extension can be freely re-used (query
extracted and placed
inside someone else's project).

Thank you.

Igor, https://github.com/MichaelDBA/pg_get_tabledef provides "pg_get_tabledef" function which can be called from SQL and therefore used from ODBC/C. This "extension" is nothing PL/PGSQL source code of the function that returns DDL. That's about it. This is how it works:

mgogala@umajor Downloads]$ psql -h postgres -f pg_get_tabledef-main/pg_get_tabledef.sql
Password for user mgogala:
DO
CREATE FUNCTION
[mgogala@umajor Downloads]$ psql -h postgres
Password for user mgogala:
psql (13.6, server 14.4)
WARNING: psql major version 13, server major version 14.
         Some psql features might not work.
Type "help" for help.

mgogala=# select pg_get_tabledef('mgogala','emp');
                           pg_get_tabledef                           
---------------------------------------------------------------------
 CREATE  TABLE mgogala.emp (                                        +
   empno smallint NOT NULL,                                         +
   ename character varying(10) NULL,                                +
   job character varying(9) NULL,                                   +
   mgr smallint NULL,                                               +
   hiredate timestamp without time zone NULL,                       +
   sal double precision NULL,                                       +
   comm double precision NULL,                                      +
   deptno smallint NULL,                                            +
   CONSTRAINT emp_pkey PRIMARY KEY (empno),                         +
   CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno)+
 ) TABLESPACE pg_default;                                           +
                                                                    +
 
(1 row)

So, you clone the Git repository, run the "CREATE FUNCTION" script and, voila, you can get the DDL for the desired table. Here is the same stuff produced by the psql utility:

mgogala=# \d emp
                           Table "mgogala.emp"
  Column  |            Type             | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
 empno    | smallint                    |           | not null |
 ename    | character varying(10)       |           |          |
 job      | character varying(9)        |           |          |
 mgr      | smallint                    |           |          |
 hiredate | timestamp without time zone |           |          |
 sal      | double precision            |           |          |
 comm     | double precision            |           |          |
 deptno   | smallint                    |           |          |
Indexes:
    "emp_pkey" PRIMARY KEY, btree (empno)
Foreign-key constraints:
    "fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno)

And here is using the function from an ODBC connection:

[mgogala@umajor Downloads]$ isql mgogala-pg
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select pg_get_tabledef('mgogala','emp');
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| pg_get_tabledef                                                                                                                                                                                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE  TABLE mgogala.emp (  empno smallint NOT NULL,  ename character varying(10) NULL,  job character varying(9) NULL,  mgr smallint NULL,  hiredate timestamp without time zone NULL,  sal double precision NULL,  comm double precision NULL,  deptno smallint NULL,  CONSTRAINT emp_pkey PR...|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
SQL> 

The function description looks like this:

mgogala=# \df pg_get_tabledef
                                                                                
                                List of functions
 Schema |      Name       | Result data type |                                  
                                                   Argument data types          
                                                                           | Typ
e
--------+-----------------+------------------+----------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------------------------------+----
--
 public | pg_get_tabledef | text             | in_schema character varying, in_t
able character varying, in_fktype tabledef_fkeys DEFAULT 'FKEYS_INTERNAL'::table
def_fkeys, in_trigger tabledef_trigs DEFAULT 'NO_TRIGGERS'::tabledef_trigs | fun
c
(1 row)


As expected, the function returns the "text" data type.


Regards


-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com