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:
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
