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

Michael,

Thanks for the pointers.
Apparently postgres doesn't actually have built in functions to dump DDL
then. I wonder how difficult it would have been to create SQL functions
out of what is basically psql or pg_dump magic.

Until then, I guess I'll have to muddle though as best I can.
rik.

On Sun, Mar 19, 2023 at 8:27 PM MichaelDBA <MichaelDBA(at)sqlexec(dot)com> wrote:

> Use "psql -E" and then use the shortcut commands to get DDL stuff. You
> will see the SQL used behind the scenes to get the stuff.
>
> \d myschema.mytable
> \df myschema.function
> etc.
>
> Regards,
> Michael Vitale
>
>
> richard coleman wrote on 3/19/2023 5:36 PM:
>
> 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.
>
>
>
> Regards,
>
> Michael Vitale
>
> Michaeldba(at)sqlexec(dot)com <michaelvitale(at)sqlexec(dot)com>
>
> 703-600-9343
>
>
>
>