From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
Date: Mon, 20 Mar 2023 08:31:19 +0100
Message-ID: <18e53273-264a-2ccf-e1ed-070fc6d7c381@jakobs.com> (raw)
In-Reply-To: <CAGA3vBt=an4RqygEvUeeLPB_s0W92hC2DtjFmxV2eKu93NWOKA@mail.gmail.com>
References: <CAGA3vBt=an4RqygEvUeeLPB_s0W92hC2DtjFmxV2eKu93NWOKA@mail.gmail.com>
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
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../18e53273-264a-2ccf-e1ed-070fc6d7c381@jakobs.com/2-OpenPGP_signature)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: holger@jakobs.com, pgsql-admin@lists.postgresql.org
Subject: Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
In-Reply-To: <18e53273-264a-2ccf-e1ed-070fc6d7c381@jakobs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox