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

Hi,

On Sun, Jul 10, 2022 at 8:09 PM Rob Sargent <robjsargent@gmail.com> wrote:
>
>
>
> > On Jul 10, 2022, at 6:16 PM, Igor Korot <ikorot01@gmail.com> wrote:
> >
> > 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
SQLand therefore 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
> >
> >
>
> Do it properly. Make it part of your coding infrastructure so it’s available in the next environment. You’ll thank
yourself

How should I do that?

Thank you.