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

Holger,

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(at)jakobs(dot)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.
>
> Regards,
>
> Holger
>
>
> --
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>
>