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

Hi,

On Sun, Jul 10, 2022 at 7:02 PM Mladen Gogala <gogala.mladen@gmail.com> wrote:
>
> On 7/10/22 17:00, Igor Korot wrote:
>
> 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
andtherefore used from ODBC/C. This "extension" is nothing PL/PGSQL source code of the function that returns DDL.
That'sabout it. This is how it works: 

I understand.

The question here - does this "extension'' is a part of standard
PostgreSQL install?
And if not - can I copy and paste that code in my program?

Thank you.

>
> 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