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