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

Michael,

Unfortunately those functions, while they may form the building blocks of a
custom function or query aren't really compatible.

The dbms_metadata.get_dependent_ddl() function lists all of the indices on
the schema & table given as an argument.

The pg_get_indexdef() on the other hand requires that I know the ordinal
number or name of every index in the table, before I can call it. Even
then it only returns the index DDL for the index I entered as an argument
to the function.

Look below for the difference in the two functions, how they are used, the
amount of knowledge you need to posses in order to run them and the
difference in the output.

Thanks,
rik.

----------------------
-- examples -----

-- postgresql
select pg_get_indexdef('cl.eix_sched_pat_id'::regclass);
I get:
CREATE INDEX eix_sched_pat_id ON cl.sched USING btree (pat_id)

-- oracle
select dbms_metadata.get_dependent_ddl('INDEX',SCHED','CL') output from
dual;

this is what I get:
CREATE UNIQUE INDEX "CL"."PK_SCHED" ON "CL"."SCHED" ("PAT_ENC_CSN_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "ZZZIXMEDIUM"
CREATE INDEX "CL"."EIX_SCHED_UPD" ON "CL"."SCHED" ("UPDATE_DATE")
PCTFREE 0 INITRANS 16 MAXTRANS 255 COMPUTE STATISTICS COMPRESS 1
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "ZZZIXMEDIUM"
CREATE INDEX "CL"."EIX_SCHED_DTTM" ON "CL"."SCHED" ("APPT_DTTM")
PCTFREE 0 INITRANS 16 MAXTRANS 255 COMPUTE STATISTICS COMPRESS 1
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "ZZZIXMEDIUM"
CREATE INDEX "CL"."EIX_SCHED_DATE" ON "CL"."SCHED" ("CONTACT_DATE")
PCTFREE 0 INITRANS 16 MAXTRANS 255 COMPUTE STATISTICS COMPRESS 1
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "ZZZIXMEDIUM"
CREATE INDEX "CL"."EIX_F_SCHED_IP_DOC_CSN" ON "CL"."SCHED"
("IP_DOC_CONTACT_CSN")
PCTFREE 0 INITRANS 16 MAXTRANS 255 COMPUTE STATISTICS COMPRESS 1
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "ZZZ_REPORTING"
CREATE INDEX "CL"."EIX_F_SCHED_PAT_ID" ON "CL"."SCHED" ("PAT_ID")
PCTFREE 0 INITRANS 16 MAXTRANS 255 COMPUTE STATISTICS COMPRESS 1
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "ZZZ_REPORTING"

On Mon, Mar 20, 2023 at 10:06 AM MichaelDBA <MichaelDBA(at)sqlexec(dot)com> wrote:

> Well, there are system funcs and such too, check out
> *https://www.postgresql.org/docs/15/functions-info.html
> <https://www.postgresql.org/docs/15/functions-info.html>. *See Table 9.71. System
> Catalog Information Functions
>
> pg_get_constraintdef()
> pg_get_functiondef()
> pg_get_indexdef()
> pg_get_triggerdef()
> pg_get_viewdef()
> ...
> ...
>
> Regards,
> Michael Vitale
>
>
>
> richard coleman wrote on 3/20/2023 9:59 AM:
>
> Ed,
>
> Actually that's where I started. Most of the answers on
> StackOverflow either don't work on the latest versions of PostgreSQL or
> didn't include the complete DDL. Most were missing indices and
> constraints. As a previous poster remarked, psql -E and then copy/pasting
> and using that as the basis for custom function(s) seems to be my only path
> forward.
>
> It's a shame that users are basically forced to write not entirely
> simplistic SQL themselves. The SQL appears to already exist in PostgreSQL
> as evidenced by the results of psql -E, it just doesn't seem that it was
> packaged into user accessible SQL functions. I've noticed that the answer
> to many questions seem to be "use this psql \ command". Is there any
> reason that all of the special psql magic functions couldn't be exposed as
> default SQL functions?
>
> Oh, well. Thanks for all the help.
> rik.
>
>
> On Mon, Mar 20, 2023 at 2:33 AM Edward J. Sabol <edwardjsabol(at)gmail(dot)com>
> wrote:
>
>> On Mar 19, 2023, at 10:49 PM, richard coleman <
>> rcoleman(dot)ascentgl(at)gmail(dot)com> wrote:
>>
>> I wonder how difficult it would have been to create SQL functions out of
>> what is basically psql or pg_dump magic.
>>
>>
>> If you ignore the answers on StackOverflow that say to use pg_dump and
>> "psql -E", there must be close to 10 other answers with various SQL
>> functions which do exactly that here:
>>
>> [image: apple-touch-icon(at)2(dot)png]
>>
>> How to generate the "create table" sql statement for an existing table in
>> postgreSQL
>> <https://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-existing-table-in-postgr>
>> stackoverflow.com
>> <https://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-existing-table-in-postgr>
>>
>> <https://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-existing-table-in-postgr>
>>
>> I recommend you read through them and test them to find the best version.
>> I hope one of them fits your needs. If so, I hope you report back here with
>> which one you went with. :-)
>>
>> Hoping this helps,
>> Ed
>>
>>
>
> Regards,
>
> Michael Vitale
>
> Michaeldba(at)sqlexec(dot)com <michaelvitale(at)sqlexec(dot)com>
>
> 703-600-9343
>
>
>
>