Hi, On Sun, Jul 10, 2022 at 7:02 PM Mladen Gogala <gogala.mladen@xxxxxxxxx> 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 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: 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
