Thanks, but I was looking for a SQL callable function(s), analogous to the Oracle dbms_metadata.get_ddl() & dbms_metadata.get_dependent_ddl() functions that return DDL in plain text from an SQL query.
It appears that PostgreSQL doesn't have that ability, at least not as a built-in function and with the suggestions of others on this list I'll have to write my own.
Thanks again,
rik.
On Mon, Mar 20, 2023 at 3:31 AM Holger Jakobs <holger@jakobs.com> wrote:
Am 19.03.23 um 22:36 schrieb richard coleman: > Hello all, > I am trying to find a simple way to get access the DDL information for > PostgreSQL tables, constraints, and indices via SQL. > > In Oracle there are the dbms_metadata.get_ddl() > and dbms_metadata.get_dependent_ddl() functions that you can call on > dual to get this information. > > EX: > -- table DDL: > select dbms_metadata.get_ddl('TABLE', 'MY_TABLE','MY_SCHEMA') as > "table_ddl" from dual; > > -- indices DDL > select dbms_metadata.get_dependent_ddl('INDEX','MY_TABLE','MY_SCHEMA') > as "table_indicies" from dual; > > -- constraints DDL > select > dbms_metadata.get_dependent_ddl('CONSTRAINT','MY_TABLE','MY_SCHEMA') > as "constraints_ddl" from dual; > > Is there an equivalent function to do the same in PostgreSQL? > > I know there are ways via pg_dump and psql, but what I need is a way > to do so via SQL. > > Thanks, > rik.
Every sane RDBMS has the INFORMATION_SCHEMA according to the ISO SQL Standard, so that you can find out all of these.
Btw, the opposite isn't true. The existence of the INFORMATION_SCHEMA alone doesn't make an RDBMS sane.
Of course there are proprietary views in pg_catalog as well. And pg_dump -s can dump the whole schema in clear text.