Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl

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.

Regards,

Holger


-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012