Hi,
On Sun, Jul 10, 2022 at 7:02 PM Mladen Gogala <[email protected]> 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
