Re: Get the table creation DDL - Mailing list pgsql-general
| From | Rob Sargent |
|---|---|
| Subject | Re: Get the table creation DDL |
| Date | |
| Msg-id | E0137597-C51C-449C-9F43-6BC472FA567F@gmail.com Whole thread Raw |
| In response to | Re: Get the table creation DDL (Igor Korot <ikorot01@gmail.com>) |
| Responses |
Re: Get the table creation DDL
(Igor Korot <ikorot01@gmail.com>)
|
| List | pgsql-general |
> 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 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 > > Do it properly. Make it part of your coding infrastructure so it’s available in the next environment. You’ll thank yourself
pgsql-general by date: