> On Jul 10, 2022, at 6:16 PM, Igor Korot <ikoro...@gmail.com> wrote:
>
> Hi,
>
>> On Sun, Jul 10, 2022 at 7:02 PM Mladen Gogala <gogala.mla...@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 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
>
>
Do it properly. Make it part of your coding infrastructure so it’s available in
the next environment. You’ll thank yourself
