pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
20+ messages / 9 participants
[nested] [flat]
* PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-19 21:36 richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 3 replies; 20+ messages in thread
From: richard coleman @ 2023-03-19 21:36 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-19 22:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
2 siblings, 0 replies; 20+ messages in thread
From: Tom Lane @ 2023-03-19 22:26 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: pgsql-admin
richard coleman <rcoleman.ascentgl@gmail.com> writes:
> I am trying to find a simple way to get access the DDL information for
> PostgreSQL tables, constraints, and indices via SQL.
There are bits of that, but it's not terribly complete or consistent
because nobody's made a concerted effort to build stuff that wasn't
driven by "what does pg_dump need". (And what pg_dump needs, for
the most part, is not simply "give me all the DDL for this object
in a black box".)
I'd suggest taking a look at the pg_get_* functions, which are
mostly documented at
https://www.postgresql.org/docs/current/functions-info.html
There are some other things on that page that might help, too.
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-20 00:27 MichaelDBA <MichaelDBA@sqlexec.com>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
2 siblings, 1 reply; 20+ messages in thread
From: MichaelDBA @ 2023-03-20 00:27 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: pgsql-admin
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@sqlexec.com <mailto:michaelvitale@sqlexec.com>
703-600-9343
Attachments:
[image/jpeg] pgadvanced3.jpg (20.6K, ../../978c008d-cad2-57e1-32ba-a7edcf4b1be9@sqlexec.com/3-pgadvanced3.jpg)
download | view image
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-20 02:49 richard coleman <rcoleman.ascentgl@gmail.com>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 1 reply; 20+ messages in thread
From: richard coleman @ 2023-03-20 02:49 UTC (permalink / raw)
To: pgsql-admin
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@sqlexec.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@sqlexec.com <michaelvitale@sqlexec.com>
>
> 703-600-9343
>
>
>
>
Attachments:
[image/jpeg] pgadvanced3.jpg (20.6K, ../../CAGA3vBtNLpGdPG=4gdzvD9zoTSz+52JUkfYCsNSi+mv5Yr+GVw@mail.gmail.com/3-pgadvanced3.jpg)
download | view image
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-20 06:33 Edward J. Sabol <edwardjsabol@gmail.com>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 1 reply; 20+ messages in thread
From: Edward J. Sabol @ 2023-03-20 06:33 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: pgsql-admin
--Apple-Mail-5BD3A5B3-8D34-4B95-9E05-EBCBDD2E67C6
Content-Type: multipart/related;
type="text/html";
boundary=Apple-Mail-0D6375A1-AF67-49BD-A1EC-4E4B48CD3D3B
Content-Transfer-Encoding: 7bit
--Apple-Mail-0D6375A1-AF67-49BD-A1EC-4E4B48CD3D3B
Content-Type: text/html;
charset=us-ascii
Content-Transfer-Encoding: quoted-printable
<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
us-ascii"></head><body dir=3D"auto"><div dir=3D"ltr"></div><div dir=3D"ltr">=
On Mar 19, 2023, at 10:49 PM, richard coleman <rcoleman.ascentgl@gmail.co=
m> wrote:</div><div dir=3D"ltr"><blockquote type=3D"cite">I wonder how di=
fficult it would have been to create SQL functions out of what is basically p=
sql or pg_dump magic.</blockquote><br></div><div dir=3D"ltr">If you ignore t=
he answers on StackOverflow that say to use pg_dump and "psql -E", there mus=
t be close to 10 other answers with various SQL functions which do exactly t=
hat here:</div><div dir=3D"ltr"><br></div><div dir=3D"ltr"><div style=3D"dis=
play: block;" class=3D""><div style=3D"-webkit-user-select: all; -webkit-use=
r-drag: element; display: inline-block;" class=3D"apple-rich-link" draggable=
=3D"true" role=3D"link" data-url=3D"https://stackoverflow.com/questions/2593=
803/how-to-generate-the-create-table-sql-statement-for-an-existing-table-in-=
postgr"><a style=3D"border-radius:10px;font-family:-apple-system, Helvetica,=
Arial, sans-serif;display:block;-webkit-user-select:none;width:228px;user-s=
elect:none;-webkit-user-modify:read-only;user-modify:read-only;overflow:hidd=
en;text-decoration:none;" class=3D"lp-rich-link" rel=3D"nofollow" href=3D"ht=
tps://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-s=
ql-statement-for-an-existing-table-in-postgr" dir=3D"ltr" role=3D"button" dr=
aggable=3D"false" width=3D"228"><table style=3D"table-layout:fixed;border-co=
llapse:collapse;width:228px;background-color:#E9E9EB;font-family:-apple-syst=
em, Helvetica, Arial, sans-serif;" class=3D"lp-rich-link-emailBaseTable" cel=
lpadding=3D"0" cellspacing=3D"0" border=3D"0" width=3D"228"><tbody><tr><td v=
ertical-align=3D"center" align=3D"center"><img style=3D"width:228px;filter:b=
rightness(0.97);height:228px;" width=3D"228" height=3D"228" draggable=3D"fal=
se" class=3D"lp-rich-link-mediaImage" alt=3D"apple-touch-icon@2.png" src=3D"=
cid:DF0BAEB5-57F4-47D5-B14E-DCC967E427AA"></td></tr><tr><td vertical-align=3D=
"center"><table bgcolor=3D"#E9E9EB" cellpadding=3D"0" cellspacing=3D"0" widt=
h=3D"228" style=3D"font-family:-apple-system, Helvetica, Arial, sans-serif;t=
able-layout:fixed;background-color:rgba(233, 233, 235, 1);" class=3D"lp-rich=
-link-captionBar"><tbody><tr><td style=3D"padding:8px 0px 8px 0px;" class=3D=
"lp-rich-link-captionBar-textStackItem"><div style=3D"max-width:100%;margin:=
0px 16px 0px 16px;overflow:hidden;" class=3D"lp-rich-link-captionBar-textSta=
ck"><div style=3D"word-wrap:break-word;font-weight:500;font-size:12px;overfl=
ow:hidden;text-overflow:ellipsis;text-align:left;" class=3D"lp-rich-link-cap=
tionBar-textStack-topCaption-leading"><a rel=3D"nofollow" href=3D"https://st=
ackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-state=
ment-for-an-existing-table-in-postgr" style=3D"text-decoration: none" dragga=
ble=3D"false"><font color=3D"#000000" style=3D"color: rgba(0, 0, 0, 1);">How=
to generate the "create table" sql statement for an existing table in postg=
reSQL</font></a></div><div style=3D"word-wrap:break-word;font-weight:400;fon=
t-size:11px;overflow:hidden;text-overflow:ellipsis;text-align:left;" class=3D=
"lp-rich-link-captionBar-textStack-bottomCaption-leading"><a rel=3D"nofollow=
" href=3D"https://stackoverflow.com/questions/2593803/how-to-generate-the-cr=
eate-table-sql-statement-for-an-existing-table-in-postgr" style=3D"text-deco=
ration: none" draggable=3D"false"><font color=3D"#A2A2A9" style=3D"color: rg=
ba(60, 60, 67, 0.6);">stackoverflow.com</font></a></div></div></td></tr></tb=
ody></table></td></tr></tbody></table></a></div></div><br></div><div dir=3D"=
ltr">I recommend you read through them and test them to find the best versio=
n. I hope one of them fits your needs. If so, I hope you report back here wi=
th which one you went with. :-)</div><div dir=3D"ltr"><br></div><div dir=3D"=
ltr">Hoping this helps,</div><div dir=3D"ltr">Ed</div><div dir=3D"ltr"><br><=
/div></body></html>=
--Apple-Mail-0D6375A1-AF67-49BD-A1EC-4E4B48CD3D3B
Content-Type: image/png;
name="apple-touch-icon@2.png";
x-apple-part-url=DF0BAEB5-57F4-47D5-B14E-DCC967E427AA
Content-Disposition: inline;
filename="apple-touch-icon@2.png"
Content-Transfer-Encoding: base64
Content-Id: <DF0BAEB5-57F4-47D5-B14E-DCC967E427AA>
iVBORw0KGgoAAAANSUhEUgAAATwAAAE8CAYAAABdBQ0GAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJ
bWFnZVJlYWR5ccllPAAAAyNpVFh0WE1MOmNvbS5hZG9iZS54bXAAAAAAADw/eHBhY2tldCBiZWdp
bj0i77u/IiBpZD0iVzVNME1wQ2VoaUh6cmVTek5UY3prYzlkIj8+IDx4OnhtcG1ldGEgeG1sbnM6
eD0iYWRvYmU6bnM6bWV0YS8iIHg6eG1wdGs9IkFkb2JlIFhNUCBDb3JlIDUuNi1jMDE0IDc5LjE1
Njc5NywgMjAxNC8wOC8yMC0wOTo1MzowMiAgICAgICAgIj4gPHJkZjpSREYgeG1sbnM6cmRmPSJo
dHRwOi8vd3d3LnczLm9yZy8xOTk5LzAyLzIyLXJkZi1zeW50YXgtbnMjIj4gPHJkZjpEZXNjcmlw
dGlvbiByZGY6YWJvdXQ9IiIgeG1sbnM6eG1wTU09Imh0dHA6Ly9ucy5hZG9iZS5jb20veGFwLzEu
MC9tbS8iIHhtbG5zOnN0UmVmPSJodHRwOi8vbnMuYWRvYmUuY29tL3hhcC8xLjAvc1R5cGUvUmVz
b3VyY2VSZWYjIiB4bWxuczp4bXA9Imh0dHA6Ly9ucy5hZG9iZS5jb20veGFwLzEuMC8iIHhtcE1N
OkRvY3VtZW50SUQ9InhtcC5kaWQ6RkE1MTZBNzU1MDE1MTFFNUEzODE5OTE2QTE2M0ExOTYiIHht
cE1NOkluc3RhbmNlSUQ9InhtcC5paWQ6RkE1MTZBNzQ1MDE1MTFFNUEzODE5OTE2QTE2M0ExOTYi
IHhtcDpDcmVhdG9yVG9vbD0iQWRvYmUgUGhvdG9zaG9wIENDIChNYWNpbnRvc2gpIj4gPHhtcE1N
OkRlcml2ZWRGcm9tIHN0UmVmOmluc3RhbmNlSUQ9InhtcC5paWQ6QTg1MTQ5NjM5NDRGMTFFNDlE
NzZCNkZGMjlBQTI4N0QiIHN0UmVmOmRvY3VtZW50SUQ9InhtcC5kaWQ6QTg1MTQ5NjQ5NDRGMTFF
NDlENzZCNkZGMjlBQTI4N0QiLz4gPC9yZGY6RGVzY3JpcHRpb24+IDwvcmRmOlJERj4gPC94Onht
cG1ldGE+IDw/eHBhY2tldCBlbmQ9InIiPz6xm0OTAAAWFUlEQVR42uzdDbxuU53A8X0nYUQplPSh
O14iRnm9hER5y0ijvJbSJEkvMtUnH3pxNZqijEJGphTRlQxKXkZMepHQCykRQmoqg1BGie78/551
Pvc4nHufc85+9rOf5/n+Pp//Z/tc5+y91nqe9Ttrr732f82aP39+BQCjwN9oAgCEBwCEBwCEBwCE
BwCEBwCEBwCEBwCEBwCEBwCEB4DwAIDwAIDwAIDwAIDwAIDwAIDwAIDwAIDwAIDwABAeABAeABAe
ABAeABAeABAeABAeABAeABAeABAeAMIDAMIDAMIDAMIDAMIDAMIDAMIDAMIDAMIDAMIDAMIDQHgA
QHgAQHgAQHgAQHgAQHgAQHgAQHgAQHgAQHgACA8ACA8ACA8ACA8ACA8ACA8ACA8ACA8ApshimuCx
zJo1SyMMIH+cu/qL4/CpiJ2WnnvzL4ehTvPnz/fB1t2/NSrhDbjo8i7l0IjDyx3L9yK2DOn9hfDg
lhbDJLsV4/D1iH8Z913eNOIjWgdGeEZ4wyS77eJwWsQKk/xI3tqeb4QHwiO8QRbdYmVEd3B+XAv5
0bsj1gvp/YrwQHiEN4iyWyUO8yI26/JXLo/YKqT3MOEhMYeHQZHdK+NwzRRkl2wecYTWgxGeEd6g
iG6JOBwVceAMTrNjjPIuNMID4RFem2W3WhzOjNhghqcayPk8fdMtLUZHdnuWW9gNajjdchFnlAce
IDygVbJ7RhxOiFi6xtPmfN7hWpfwgFYRt573xGGfHpz6kLJ+D4QHtEp658Xh6JpPmxO0p4X0VtLC
hAe0jUOqzruxdZJvZswL6T1J8xIe0KZRXiYAyIcX99Z86i0r83kjiWUpExvEspTWEaOxXeJwds2n
zS/+DiHVi9tab33TCA+jOdI7Jw6frPtvW9WZz3u2FiY8oG28N+Lqms+5QpGe+TzCA1o1ynsoDntU
9c/nvTTiA1p4NDCHN7FBzOG1mh7N5/01YruQ6qVtqqu+aYQHI71ezOdlPzi9ZFAG4QGtIpN//rDm
cz6rSM98HuEBrRrl/TkOu0fcX/OpzecNOebwJjaIObyBIUZju8bhyzWfNufzXhZSvazf9dM3jfCA
8SO9s6rOXrR194kvhkyfqYUJD2gb767qn8/LxcinlT1vQXhAa0Z5vZrP27bqbPCNIcIc3sQGMYc3
bWJE9LI4PNKP+a+4dkrvSzWfNufzXhr1+WY/2lPfNMJDe2W3ZhxyTu3i+O/9+zDSy70vPt2D/jHP
fJ4RnhEexssuU7Jn3ro1xv3z8REHhYgeabAcS5ZyvLDmU389YvuoS6OdRd80wkP7ZPfkMrJbY8L/
envERfH/n97gKO9Pcdgti1XzqXM+72CfNuEBx0VsPcn/2yZHXOV2tynp3RSH/Xpw6iOiHlv4uAkP
ozu6OygOi5qve16RXmOb54T0zqjqn8/LV85yq8flffKEh9GT3cur7jfZWTbiwviddzRYxJTxtTWf
8zkRX4h6mOglPIyQ7NbO0c4Uvz/5s8fG754UsXgDo7ycz8v8eQ/UfOodKvN5A4untBMbxFPaRcku
b+ky8/DsGZwm17W9OqR0dwPl3TtHZTWfNp88bxXl/04vy65vGuGhv7LLkdnZM5Rd8pKI78f51mlg
pHdaHD5b82nH5vOe4VtBeBheTox4cU3nSmleEdLYqYFy59zhT2o+Z87nnWI+j/AwnKO73ETnn2o+
7TIRXynn7uUo78Gqsz6v7vm8lPW7fTsGB3N4ExvEHN4Tye4VcTi3x38gT414c0kG0Kt69GI+7+GI
LaPcV9RdXn2T8AivednlvN3PI57bwOVSGruEPH7Xw/rkfN4baz7tHRHrRbnvITy3tBhgyvaImQXl
hgYu96KIq0JK6/fwGgdGXF/zOVeuzOcRHoZGerfEYZOICxq43CoR3ynbMfaiLjmPl6mk/q/mU5vP
c0vrlnbIbm9zOcZREe9q6JIfjDiiF1lKoi5viMPnaj7tXyK2iPJe5ZaW8AhveMSXsjgp4skNXC7f
6HhjedJadz0+H4d9aj7tbREbRHl/T3huaTEct7gpitzS8M4GLrdnxLdDTiv14Nxvq+qfz5sdcbL5
PMLDcEkvX6uaE/HjBi63YdV5M2PjmuvQq/m8f4x4p28J4WG4pHd7HDaP+EoDl8udxL4V0tur5jr8
tOo8ua2bo6Ksc3xL2oU5vIkNYg5vypTbtw9HHNLQJfNaH6jzYUbUId+5fW3N5bytmsF8nr5phId2
jvTmRxxahPGnBi75voizQ1JPqfGcb4m4seZyzq7qT1wAwkNLxPfFqpMJ5bcNXC7nyS4P6T23prLn
Phi79UDYu0QZ3+7bQXgYTunlGrSNIn7QwOVyd7J8M2Ozmsp+XdXJrFI3R0cZN/TtIDwMp/R+HYct
I85s4HK5Z+w3ytrAOsr+mTicXnMZ833kM6OMT/PtIDwMp/RyqUeuoftgA5dLoXwuhHJUeRtkpuR8
3s9rLuOqlfm8vuMp7cQG8ZS2dkJCu8bhlIilGrhcvu+7Vwj3/hmWeb2qs6n3EjWX7x1RtuO7+UF9
k/AIb3CllxlQcr3eyg1cLt+eeEWI5RczLHOO9P695rJl9pnNomyLnOPUNwmP8AZbeivG4ZyITRu4
XOamy42CLpthmfNd3j1qLtvNVWd93h8Ir1nM4aExooPncpWtIk5r4HK5wc7FIaz9Z3ie/Yqg6mT1
iP/wjTDCM8IbndFe7u36rw390c05s4NCuI9Ms6y9ms87IMp0ohEe4RHeaEgv98rIxcpLN3C5SyJ2
n+5rXj2az8v9OzaNMl1DeIRHeKMhvb+Pw3nVzPe67YZcarJzCObGaZY11xXuVnOZJp3P0zfrxxwe
+kp09NwvNtM+fbuByz0v4soQ13bT/P2cz/tFzWUyn0d4GDHp3RWHbRrq+Pm2w4UhvQOnUc77qk7+
vIdqLtMeNTxcgVtat7TjbscyJfu9EZ+Ijvs/LS7nQXE4uqE/xinYt5ed2aZSxkwGcFzNZcmkBXPK
+7xuaQmP8GYgkbxturFIJDeayc2oPxad64aWljdvOc8so7Fe882IXcsocyplPCsOr665LPkZbVQy
txCeW1pMk/eM+6xz453ciPr66LTnRryohbe4F1edxck3NXC5TGd1dbTDOlP8vX2r+ufz1ow40dfV
CM8Ib/qjpUyNfmu18DVkuT/FkRHn92JLxBmU/elx+HLV2Qi81+RT0tdG/c+bQvky5dN3q07ygjrZ
L7O26JtGeJg676wWvWB2i6qzNOS66MSvj1i8DQUva+Z2iPhUA5dbJiJHvO+dQvnyfdiDe1CW46Ic
6/rqGuEZ4U1thLRs1dlXYapzYXdEfCLipLH5pBbUJRf+5oOCxRq43BfKKOvPXZQrvzBnV50MzHVy
7VMOu2k9PdIID93zlmp6E/+Z0SSflN4RHfqIiGe2YLSXc1v5MOOeBi73uqqTVPRZXZQrRwxvLH9Y
6iIfXuzj62uEZ4TX/Yhoyaozd7diDafLJROfj/h4dPBb+lyv1eLwtYi1GrhcjnRfGXX+URflyi0Z
cy70yTWMLt+aI2t9k/AIr3sx5ELWup/4/TXiPyOO7CafWw/r9tQ4zIvYsYHLZebm10V9z+6iXLmG
8JhpXic3BX9bXOeUsX/QNwmP8LoTwpPKbdFqPbzMpRFHlSUk/apjPll+d0OXzFT1RyzsKfYM5vNy
sfHuE9dF6puER3jdySATVp7R0OXydu9jEV+ODvtwH+r6hjicVMOtZDdkm+5b9uuYrDy5lObaqvvM
zp+uOqmrHrc9pL5JeITXnQR+GIf1G77sbREfjzg5Ou+DDdd38zKyauLhSt7K77yw1/PKYu5vVQt/
onx/kedZk/2Avkl4hLfozr99HC7qYxHujjg24lPRme9usN65IfdXI17QwOV+k7etZQ/eycrznjLy
fSKujtgjfv/WhV1E36wfy1KGj4P7fP3lIg6PuD06/bERqzRx0ZDH7XHIkd65DVwu3175ZtRtr4X8
TC7r+dok/77FomQHIzwjvEWPcnJpxJUtK1amVc+5r3zA8eMG2iA/wA9HHNJQ/TJN/fuf6GFGlCX3
1chsxiuXke8+8XPnd3tifZPwCG/hnX1uHA5rcREvLOK7rIG2yNHXyRFLNlCvHFXuHfV64AnKkfN5
RxTZ/WoqJ9U3CY/wFt3RM3twvg+aqYvaWpmc+zoq4pyQwF97POLNvXBXbKBOOXrdudxa14K+SXiE
131nX6PqpIV6fUOjnOmQ6Z9yYv/Ubt5bnWY7rFR1HmZs2EB97ozYJeryXcIjPMLrj/jyfdDMmPLW
qpmEmtMh96v9ZMSJIYt7e9AGS5Xb2z0aqEsmWH1z1OPzhEd4hNc/8eVWiPm62T9HPKelxcycdLkQ
95i609CXhxnvj/hQQ7e3G081dTzhER7h1S++zHX3mqozz/f8lhZzLA19Jiv4Wc31z7nNUyOW6uFt
7Zw65vL0TcIjvHpHPDtVnXV7m7e0mPnlzMSkR9Y1L1bqnm+h5MOMlWsub85Dbh1lvaKWyuubhEd4
PZHf5kV8KcC2NsDlER+takpDH3XOJ7f5Olqde3rk0pTTa7O9vkl4hNdT8a1ddZ7s7l018zL+dLi+
6ixpmTfTObKob6a+/0yp70z5SJTn0FqHt/om4RFeI+LLhxqZ2y0zJi/d0mLmIt5MQ//pmaahL/tY
fKSa/quWOVLcte4NkPRNwiO8ZsWXe2IcEHFg1czi3emQy1hOiDg2hPO7GdT1FXH44jQEn+mxtlhY
yijCIzzCGyzx5cLl15fb3TVaWsyxNPRHh3xunmY9c2/afOF/dpe/kusHN57qK2OER3iENxjiy1u+
V1WdJS0bt7SY+YXOHHP5zu73p1HH5atOGvstF/GjmfNvy+lcg/AIj/AGT35bF/Ht0OJi/nfVWdJy
8RTrlmsVj4/YbyE/tmec90s9Nbe+SXiE1zrxZcLNXNKSr209qaXFzJTruf/FlNLQR93ylbx/qx7/
MGNunOfwng9V9U3CI7zWii8zDueGOrlH61NaWszbqk4CzpO7fcgQ9cq9cM+sFryHnLn9XlP3E1nC
IzzCG0zx5RxYJirIJ7vLtbSYY2noTwhx3dVFnXIP3Hwz476IlzS1Z4e+SXiENzji+9s47FtGfbNb
Wswc5X226jzZvX0R9cndyJaIn/ttU4XTNwmP8AZPfLlz125VZ57vhS0tZqahzwcQRzaRhp7wCI/w
RkN+2xXxvbTFxcwd33JJyzcIj/AID3WIb6Oqs6Rl16q9yQpyK8V8Z/fsXqahJzzCI7zREd/qVWeO
7w1Ve9PQHxbC+1A/LqxvEh7hDaf4nlktSEO/bIuKlolIV+3Vq2OE1zw24h4sMQzl5xVCuTPifVUn
IWeO+H7VkqKd1i/ZwQhv5Ed4IbzcjyJf4M/Ntq8ei+iU9w2Z2PPVrj2rzgOOtftUjOwY69SdYt4I
j/AIr3sRzCsimMiNRX5XleOPerXtYcP1zQ/jH4r4tmj48rln7qv6WX99k/BGXXiZ9mi1Ln403xfN
90e/XySYI8IbogM/MsDy26zqPNnduWrmye6m0V5XEh7hEV5/Ony+pnXXDE7xQMQPxo0C81b41gEU
3/PLbf3rqt6lob8s2mbrftdV3yS8URbey+NwQc2nvWucAK8qEvzfARHfStWCNPTL1Hz6HaId/ovw
CI/w+tfBD4vD3AYudVu14IFI3tL9cKZ7RvS4XXIZy/5FfnWkob8m6rt+G+qmbxLeKAvv/Djs2IdL
51sGPysjwIycF7w2pPCXlrVP7kA2lob+eTM41V5RtzMIj/AIr78dOm81l29JcfIJ8DXjRoEpwRub
yBHXRTvlWsVdqs4DjjlT/PVbItZsy8MdfZPwRlJ40Yn/Lg6/aHnT3T/uVvhREYY4ft3ndntJ1VnS
8vIuf+WAKPOJbWlQfZPwRlV4ufZu3gA252+qx64PzIciv+9D+61bxJftOFka+sxzt2pTyT0Jj/AI
b/IOm2nJ3zUkTXxztWA+cGyR9IMNteMqpR3fVD0+Df0hUY6Ptqmh9E3CG1XhfScOmw9pk+d82XXV
ggciOSf4017Oo5U1jWNp6Jcvt+PPjWveS3iER3j9lV1mDM53ZZcaoY8hU6//aPxIMGR0Sw/aNtPQ
56ZDi8f5j2lbI+ibhDeKwluvdP5R555qwXzgo6PBJveXILzhYDFN0HrmaIJHeUbE9iXG/hjcUS1Y
FjMmwT9oKhBeH/6q1jRa3NinMikrl9h17OMLCd4wYST442HIHAO3tCNxexwd+Lw47KQ1p81DVSdz
zPjlMTf0a58Kt7SER3iLll7uibpRub3duByfrYWn36RFfFeUTMuER3hoi/AmkeBzivjmjJPhU7X6
lOg6UUCT/WTse6NvEh4RTi7APOma4+SXkU94l9Dik3JSCG9/wiM8DOjIb4IEM0HmC8bdBmdkAk2b
N3V4Uwjvs4RHeBgC4U0iwXylasOITcaNBmeP6EexbgjvJ/0WHOERXt+49JJLZtwgL9tmm1ltFd4k
ElyhjALHjwSXH/KPOlPeP22yV9gW1S/q+J5ss+22s9oyqhwVrMNDVdK6X1CNSyEfEpxdPfap8Ab5
o0NU7R8M8qZGIDzUK8Hbqk669zOLADOt0lrlVnhsNPjCAf4OXeVTJjxgMgHmaOinJU4uEswnwLms
Y6NxIsz06rMGoEqER3jAlCSYr2x9r8TxRYJPK+IbeyCSIlyphcW/0idIeMBMJZiprC4pURUJPrtI
cJNxMnx6H4v5uyjnL31ahAf0QoKZ6v2rJcYWSa9eLXgokpEPRZZsqEhX+1QID2hKgLne4qYSpxcJ
5ndx3XECzNHg2tXke1C4nQXhYWAl+HDVSXSacVKRYC6SXq/Ib+yd4dVquJwHFoQHtE6CuTj48hJV
keBy1WMXSefxWW5pQXgYRgneHYeLSoxJcJXqsfOBGZMtkr6pH1tFgvCAuiSYT1wzzioCzOQIa00Y
BeYi6cWN7ggPGDYBZjbj60ucUiSYi6Qzc8yftBDhAcMuwT8b3UFeNACEBwBuadEz5D/rH23LUQjC
G6lO98e5q7Nfj1h67s3s5pYWAAgPAAgPAAgPAAgPAAgPAAgPAAgPAAgPAAgPAOEBAOEBAOEBAOEB
AOEBAOEBQGNIANpSJKkEjPAAgPAAgPAAgPAAEB4AEB4AEB4AEB4AEB4A9AFvWvSB+fPnawTACA8A
CA8ACA8ACA8ACA8A4QHAkGNZSg+49JJLrDsBjPAAgPAAgPAAgPAAgPAAgPAAEB4AEB4AEB4AEB4A
EB4AEB4AEB4AEB4AEB4AEB4APMose6QCMMIDAMIDAMIDAMIDAMIDAMIDAMIDAMIDAMIDAMIDQHgA
QHgAQHgAQHgAQHgAQHgAQHgAQHgAQHgAQHgACA8ACA8ACA8ACA8ACA8ACA8ACA8ACA8ACA8ACA8A
4QEA4QEA4QEA4QEA4QEA4QEA4QEA4QEA4QEA4QEA4QEgPAAgPAAgPAAgPAAgPAAgPAAgPAAgPAAg
PAAgPACEBwCEBwCEBwCEBwCEBwCEBwCEBwCEBwCEBwCEB4DwAGA0+H8BBgC+uYmSDNLUEgAAAABJ
RU5ErkJggg==
--Apple-Mail-0D6375A1-AF67-49BD-A1EC-4E4B48CD3D3B--
--Apple-Mail-5BD3A5B3-8D34-4B95-9E05-EBCBDD2E67C6--
Attachments:
[image/png] apple-touch-icon@2.png (6.4K, ../../A6F7CC72-F6AC-49B6-8DEC-19787D18D7F5@gmail.com/2-apple-touch-icon@2.png)
download | view image
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-20 07:31 Holger Jakobs <holger@jakobs.com>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
2 siblings, 1 reply; 20+ messages in thread
From: Holger Jakobs @ 2023-03-20 07:31 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-20 13:59 richard coleman <rcoleman.ascentgl@gmail.com>
parent: Edward J. Sabol <edwardjsabol@gmail.com>
0 siblings, 1 reply; 20+ messages in thread
From: richard coleman @ 2023-03-20 13:59 UTC (permalink / raw)
To: pgsql-admin
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@gmail.com>
wrote:
> On Mar 19, 2023, at 10:49 PM, richard coleman <rcoleman.ascentgl@gmail.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@2.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-ex...;
> stackoverflow.com
> <https://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-ex...;
>
> <https://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-ex...;
>
> 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
>
>
Attachments:
[image/png] apple-touch-icon@2.png (6.4K, ../../CAGA3vBsD_xiwuXWua6bceGToc9ZX1fmOfiaDXkw29zfqLhQ=Uw@mail.gmail.com/3-apple-touch-icon@2.png)
download | view image
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-20 14:02 richard coleman <rcoleman.ascentgl@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 20+ messages in thread
From: richard coleman @ 2023-03-20 14:02 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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@jakobs.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
>
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-20 14:06 MichaelDBA <MichaelDBA@sqlexec.com>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 1 reply; 20+ messages in thread
From: MichaelDBA @ 2023-03-20 14:06 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: pgsql-admin
Well, there are system funcs and such too, check out
*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@gmail.com <mailto:edwardjsabol@gmail.com>> wrote:
>
> On Mar 19, 2023, at 10:49 PM, richard coleman
> <rcoleman.ascentgl@gmail.com <mailto:rcoleman.ascentgl@gmail.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:
>
> apple-touch-icon@2.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-ex...;
> stackoverflow.com
> <https://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-ex...;
>
>
> 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@sqlexec.com <mailto:michaelvitale@sqlexec.com>
703-600-9343
Attachments:
[image/png] image.png (5.2K, ../../ac9c17a9-016a-e84b-1ef3-14ce4380f05d@sqlexec.com/3-image.png)
download | view image
[image/jpeg] pgadvanced3.jpg (20.6K, ../../ac9c17a9-016a-e84b-1ef3-14ce4380f05d@sqlexec.com/4-pgadvanced3.jpg)
download | view image
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-20 14:30 Holger Jakobs <holger@jakobs.com>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 1 reply; 20+ messages in thread
From: Holger Jakobs @ 2023-03-20 14:30 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Am 20.03.23 um 15:02 schrieb richard coleman:
> 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.
>
Rik,
But you realize that on the other hand a PostgreSQL user switching to
Oracle could say:
"It appears that Oracle lacks PostgreSQL's proprietary views showing the
structure of the database."
Actually, nothing besides the ISO standard INFORMATION SCHEMA is in any
way standardised. The SQL standard is not comprehensive here and leaves
a lot to the implementor.
It's not difficult to write table-valued functions returning the DDL of
database objects, but it's a lot of work.
Regards,
Holger
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../../e26607cb-0591-e5d1-cf5b-4cefb627f91e@jakobs.com/2-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-20 14:36 Thomas Kellerer <shammat@gmx.net>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 20+ messages in thread
From: Thomas Kellerer @ 2023-03-20 14:36 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Holger Jakobs schrieb am 20.03.2023 um 15:30:
> It's not difficult to write table-valued functions returning the DDL
> of database objects, but it's a lot of work.
Someone already did the heavy lifting ;)
https://github.com/MichaelDBA/pg_get_tabledef
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-20 14:48 richard coleman <rcoleman.ascentgl@gmail.com>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 1 reply; 20+ messages in thread
From: richard coleman @ 2023-03-20 14:48 UTC (permalink / raw)
To: pgsql-admin
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@sqlexec.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@gmail.com>
> wrote:
>
>> On Mar 19, 2023, at 10:49 PM, richard coleman <
>> rcoleman.ascentgl@gmail.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@2.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-ex...;
>> stackoverflow.com
>> <https://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-ex...;
>>
>> <https://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-ex...;
>>
>> 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@sqlexec.com <michaelvitale@sqlexec.com>
>
> 703-600-9343
>
>
>
>
Attachments:
[image/png] image.png (5.2K, ../../CAGA3vBv-BkLgTyf-DRW9DYjnF2=BMuKOpT0Gp7cCD1oWDvSxqw@mail.gmail.com/3-image.png)
download | view image
[image/jpeg] pgadvanced3.jpg (20.6K, ../../CAGA3vBv-BkLgTyf-DRW9DYjnF2=BMuKOpT0Gp7cCD1oWDvSxqw@mail.gmail.com/4-pgadvanced3.jpg)
download | view image
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-20 14:49 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Thomas Kellerer <shammat@gmx.net>
0 siblings, 0 replies; 20+ messages in thread
From: MichaelDBA @ 2023-03-20 14:49 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; +Cc: pgsql-admin@lists.postgresql.org
hehehe, thats me
Thomas Kellerer wrote on 3/20/2023 10:36 AM:
> Holger Jakobs schrieb am 20.03.2023 um 15:30:
>> It's not difficult to write table-valued functions returning the DDL
>> of database objects, but it's a lot of work.
> Someone already did the heavy lifting ;)
>
> https://github.com/MichaelDBA/pg_get_tabledef
>
>
Regards,
Michael Vitale
Michaeldba@sqlexec.com <mailto:michaelvitale@sqlexec.com>
703-600-9343
Attachments:
[image/jpeg] pgadvanced3.jpg (20.6K, ../../dd5294a2-fc9e-187d-d14d-f5fe285827fc@sqlexec.com/3-pgadvanced3.jpg)
download | view image
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-20 15:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 1 reply; 20+ messages in thread
From: Tom Lane @ 2023-03-20 15:10 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: pgsql-admin
richard coleman <rcoleman.ascentgl@gmail.com> writes:
> 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.
I'm not finding this terribly convincing, because I don't exactly see
what is the use-case for having that DDL in isolation. Or in other
words, what is your use-case that you find pg_dump so unsatisfactory
for? It knows all this stuff, and it also knows things like which
other objects a given object depends on and how to cope with circular
dependencies. Most of the reasons that I can think of for wanting
the pg_get_*def functions that we don't already have seem to reduce
to building partially-baked versions of pg_dump.
If your concern is that you can't get pg_dump to slice up the schema
in exactly the way you want, adding some more options to it might be
an easier sell than implementing/maintaining a bunch of duplicate
functionality. We just got done adding [1], for example.
regards, tom lane
[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a563c24c9574b74f4883c004c89275bba...
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-20 15:48 richard coleman <rcoleman.ascentgl@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 20+ messages in thread
From: richard coleman @ 2023-03-20 15:48 UTC (permalink / raw)
To: pgsql-admin
Tom,
My use case is copying and maintaining the same tables in Oracle and
PostgreSQL as well as detecting and applying DDL changes between the two
systems.
I can get the Oracle side simply enough by making use of
the dbms_metadata.get_ddl and dbms_metadata.get_dependent_ddl functions. I
am trying to do the same without having to ssh into a server, then run
pg_dump, and then rummage around in the results, extract what I need, copy
it back to my workstation and compare it to the Oracle DDL. You can get
that data from PostgreSQL in SQL, but you have to write something on the
order of what Michael's done in order to do so.
So my concern is that I can't get this information simply from SQL. I know
it exists, I can see much of it when I run psql with the -E flag. I know
that it exists in pg_dump. I know that pieces of it are scattered across
various system tables in the database itself. I feel that something this
basic should be exposed via standard default functions. Unfortunately, it
seems that a lot of functionality is locked behind psql magic commands, or
in ancillary tools like pg_dump.
I hope that helps clear things up somewhat.
rik.
On Mon, Mar 20, 2023 at 11:10 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> richard coleman <rcoleman.ascentgl@gmail.com> writes:
> > 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.
>
> I'm not finding this terribly convincing, because I don't exactly see
> what is the use-case for having that DDL in isolation. Or in other
> words, what is your use-case that you find pg_dump so unsatisfactory
> for? It knows all this stuff, and it also knows things like which
> other objects a given object depends on and how to cope with circular
> dependencies. Most of the reasons that I can think of for wanting
> the pg_get_*def functions that we don't already have seem to reduce
> to building partially-baked versions of pg_dump.
>
> If your concern is that you can't get pg_dump to slice up the schema
> in exactly the way you want, adding some more options to it might be
> an easier sell than implementing/maintaining a bunch of duplicate
> functionality. We just got done adding [1], for example.
>
> regards, tom lane
>
> [1]
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a563c24c9574b74f4883c004c89275bba...
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-20 16:10 Thomas Kellerer <shammat@gmx.net>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 1 reply; 20+ messages in thread
From: Thomas Kellerer @ 2023-03-20 16:10 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
richard coleman schrieb am 20.03.2023 um 16:48:
> My use case is copying and maintaining the same tables in Oracle and
> PostgreSQL as well as detecting and applying DDL changes between the
> two systems.
We are using Liquibase as a schema migration tool which allows us to
specify a single "changelog" (using their XML format) that can be
applied to Oracle and Postgres alike.
So we never need to "compare" definitions, we just run the changelog
script to update the database schema.
Some differences in e.g. available data types can be dealt with using
Liquibase's properties or if everything else fails using different
changesets for Postgres and Oracle (which is typically only needed
for views and procedural code).
Of course it might not be feasible to re-structure your existing
infrastructure for schema migrations to use a completely different
approach.
Thomas
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-20 16:39 richard coleman <rcoleman.ascentgl@gmail.com>
parent: Thomas Kellerer <shammat@gmx.net>
0 siblings, 1 reply; 20+ messages in thread
From: richard coleman @ 2023-03-20 16:39 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Thomas,
Thanks for letting me know about this product. Looking at their page
doesn't fill me with a sense of comfort. There's too little information,
nothing clear about on prem. vs in cloud, no pricing information, etc.
Just a lot of marketing fluff and directions to contact their sales people.
Of course, this doesn't address the fundamental concern which is that
currently PostgreSQL makes it needlessly difficult to get basic database
information, in this case DDL via SQL. Liquibase, like pg_dump, is yet
another utility needed to fill in for this gap in PostgreSQL native
functionality.
I've resigned myself to having assemble the information I need by hand. I
can only hope that the PostgreSQL developers see their way to including it
in future versions.
Thanks again,
rik.
On Mon, Mar 20, 2023 at 12:10 PM Thomas Kellerer <shammat@gmx.net> wrote:
> richard coleman schrieb am 20.03.2023 um 16:48:
> > My use case is copying and maintaining the same tables in Oracle and
> > PostgreSQL as well as detecting and applying DDL changes between the
> > two systems.
> We are using Liquibase as a schema migration tool which allows us to
> specify a single "changelog" (using their XML format) that can be
> applied to Oracle and Postgres alike.
>
> So we never need to "compare" definitions, we just run the changelog
> script to update the database schema.
>
> Some differences in e.g. available data types can be dealt with using
> Liquibase's properties or if everything else fails using different
> changesets for Postgres and Oracle (which is typically only needed
> for views and procedural code).
>
>
> Of course it might not be feasible to re-structure your existing
> infrastructure for schema migrations to use a completely different
> approach.
>
> Thomas
>
>
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-20 16:50 Ron Johnson <ronljohnsonjr@gmail.com>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 1 reply; 20+ messages in thread
From: Ron Johnson @ 2023-03-20 16:50 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Did you try https://github.com/MichaelDBA/pg_get_tabledef?
On Mon, Mar 20, 2023 at 12:39 PM richard coleman <
rcoleman.ascentgl@gmail.com> wrote:
> Thomas,
> Thanks for letting me know about this product. Looking at their page
> doesn't fill me with a sense of comfort. There's too little information,
> nothing clear about on prem. vs in cloud, no pricing information, etc.
> Just a lot of marketing fluff and directions to contact their sales people.
>
> Of course, this doesn't address the fundamental concern which is that
> currently PostgreSQL makes it needlessly difficult to get basic database
> information, in this case DDL via SQL. Liquibase, like pg_dump, is yet
> another utility needed to fill in for this gap in PostgreSQL native
> functionality.
>
> I've resigned myself to having assemble the information I need by hand. I
> can only hope that the PostgreSQL developers see their way to including it
> in future versions.
>
> Thanks again,
> rik.
>
> On Mon, Mar 20, 2023 at 12:10 PM Thomas Kellerer <shammat@gmx.net> wrote:
>
>> richard coleman schrieb am 20.03.2023 um 16:48:
>> > My use case is copying and maintaining the same tables in Oracle and
>> > PostgreSQL as well as detecting and applying DDL changes between the
>> > two systems.
>> We are using Liquibase as a schema migration tool which allows us to
>> specify a single "changelog" (using their XML format) that can be
>> applied to Oracle and Postgres alike.
>>
>> So we never need to "compare" definitions, we just run the changelog
>> script to update the database schema.
>>
>> Some differences in e.g. available data types can be dealt with using
>> Liquibase's properties or if everything else fails using different
>> changesets for Postgres and Oracle (which is typically only needed
>> for views and procedural code).
>>
>>
>> Of course it might not be feasible to re-structure your existing
>> infrastructure for schema migrations to use a completely different
>> approach.
>>
>> Thomas
>>
>>
>>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-21 06:15 jian he <jian.universality@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 20+ messages in thread
From: jian he @ 2023-03-21 06:15 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: richard coleman <rcoleman.ascentgl@gmail.com>; pgsql-admin@lists.postgresql.org
On Mon, Mar 20, 2023 at 10:35 PM Ron Johnson <ronljohnsonjr@gmail.com>
wrote:
> Did you try https://github.com/MichaelDBA/pg_get_tabledef?
>
> On Mon, Mar 20, 2023 at 12:39 PM richard coleman <
> rcoleman.ascentgl@gmail.com> wrote:
>
>> Thomas,
>> Thanks for letting me know about this product. Looking at their page
>> doesn't fill me with a sense of comfort. There's too little information,
>> nothing clear about on prem. vs in cloud, no pricing information, etc.
>> Just a lot of marketing fluff and directions to contact their sales people.
>>
>> Of course, this doesn't address the fundamental concern which is that
>> currently PostgreSQL makes it needlessly difficult to get basic database
>> information, in this case DDL via SQL. Liquibase, like pg_dump, is yet
>> another utility needed to fill in for this gap in PostgreSQL native
>> functionality.
>>
>> I've resigned myself to having assemble the information I need by hand.
>> I can only hope that the PostgreSQL developers see their way to including
>> it in future versions.
>>
>> Thanks again,
>> rik.
>>
>> On Mon, Mar 20, 2023 at 12:10 PM Thomas Kellerer <shammat@gmx.net> wrote:
>>
>>> richard coleman schrieb am 20.03.2023 um 16:48:
>>> > My use case is copying and maintaining the same tables in Oracle and
>>> > PostgreSQL as well as detecting and applying DDL changes between the
>>> > two systems.
>>> We are using Liquibase as a schema migration tool which allows us to
>>> specify a single "changelog" (using their XML format) that can be
>>> applied to Oracle and Postgres alike.
>>>
>>> So we never need to "compare" definitions, we just run the changelog
>>> script to update the database schema.
>>>
>>> Some differences in e.g. available data types can be dealt with using
>>> Liquibase's properties or if everything else fails using different
>>> changesets for Postgres and Oracle (which is typically only needed
>>> for views and procedural code).
>>>
>>>
>>> Of course it might not be feasible to re-structure your existing
>>> infrastructure for schema migrations to use a completely different
>>> approach.
>>>
>>> Thomas
>>>
>>>
>>>
Yech, I tried.
I use the following table for a test.
CREATE TABLE test_table_def(
a text COLLATE "german_phonebook"
,b bigint generated by default as identity primary key
WITH (fillfactor=70)
,c int default 7 check (c > 1)
,d int references test0(test0_d) on delete cascade on update cascade
,e text default 'hello'
,UNIQUE(e) WITH (fillfactor=70)
,f text generated always AS(
case
when c < 10 then 'within10'
when c < 20 and c >= 10 then '10 to 20'
when c >= 20 then 'largerthan20'
end
) STORED
)WITH (fillfactor=70);
--------
for now, *pg_get_tabledef *lacks generation_expression, and index storage
parameter info.
Since tables are related to so many objects, using pg_dump is the right
choice.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
@ 2023-03-21 13:58 Keith <keith@keithf4.com>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 0 replies; 20+ messages in thread
From: Keith @ 2023-03-21 13:58 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: pgsql-admin
So, this doesn't solve the issue of finding the object definitions via SQL,
but pg_extractor provides a way to get all the individual object
definitions output into organized folders/files. Each object goes into its
own file and things like tables include all their dependent objects in that
file. I used this extensively for checking database schema into git to
track changes, something nearly impossible with a single dump file since
objects are in an order that can change at any time.
https://github.com/omniti-labs/pg_extractor
It's also not really doing anything special other than taking advantage of
pg_restore options to get a catalog list (-l) then feed that object list
back into itself (-L) to only get the specified object. So if this tool
isn't doing quite what you need, knowing that might help find a solution to
what you're looking for.
On Mon, Mar 20, 2023 at 11:49 AM richard coleman <
rcoleman.ascentgl@gmail.com> wrote:
> Tom,
>
> My use case is copying and maintaining the same tables in Oracle and
> PostgreSQL as well as detecting and applying DDL changes between the two
> systems.
> I can get the Oracle side simply enough by making use of
> the dbms_metadata.get_ddl and dbms_metadata.get_dependent_ddl functions. I
> am trying to do the same without having to ssh into a server, then run
> pg_dump, and then rummage around in the results, extract what I need, copy
> it back to my workstation and compare it to the Oracle DDL. You can get
> that data from PostgreSQL in SQL, but you have to write something on the
> order of what Michael's done in order to do so.
>
> So my concern is that I can't get this information simply from SQL. I
> know it exists, I can see much of it when I run psql with the -E flag. I
> know that it exists in pg_dump. I know that pieces of it are scattered
> across various system tables in the database itself. I feel that something
> this basic should be exposed via standard default functions.
> Unfortunately, it seems that a lot of functionality is locked behind psql
> magic commands, or in ancillary tools like pg_dump.
>
> I hope that helps clear things up somewhat.
> rik.
>
> On Mon, Mar 20, 2023 at 11:10 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> richard coleman <rcoleman.ascentgl@gmail.com> writes:
>> > 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.
>>
>> I'm not finding this terribly convincing, because I don't exactly see
>> what is the use-case for having that DDL in isolation. Or in other
>> words, what is your use-case that you find pg_dump so unsatisfactory
>> for? It knows all this stuff, and it also knows things like which
>> other objects a given object depends on and how to cope with circular
>> dependencies. Most of the reasons that I can think of for wanting
>> the pg_get_*def functions that we don't already have seem to reduce
>> to building partially-baked versions of pg_dump.
>>
>> If your concern is that you can't get pg_dump to slice up the schema
>> in exactly the way you want, adding some more options to it might be
>> an easier sell than implementing/maintaining a bunch of duplicate
>> functionality. We just got done adding [1], for example.
>>
>> regards, tom lane
>>
>> [1]
>> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a563c24c9574b74f4883c004c89275bba...
>>
>
^ permalink raw reply [nested|flat] 20+ messages in thread
end of thread, other threads:[~2023-03-21 13:58 UTC | newest]
Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-19 21:36 PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl richard coleman <rcoleman.ascentgl@gmail.com>
2023-03-19 22:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-20 00:27 ` MichaelDBA <MichaelDBA@sqlexec.com>
2023-03-20 02:49 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-03-20 06:33 ` Edward J. Sabol <edwardjsabol@gmail.com>
2023-03-20 13:59 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-03-20 14:06 ` MichaelDBA <MichaelDBA@sqlexec.com>
2023-03-20 14:48 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-03-20 15:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-20 15:48 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-03-20 16:10 ` Thomas Kellerer <shammat@gmx.net>
2023-03-20 16:39 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-03-20 16:50 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-03-21 06:15 ` jian he <jian.universality@gmail.com>
2023-03-21 13:58 ` Keith <keith@keithf4.com>
2023-03-20 07:31 ` Holger Jakobs <holger@jakobs.com>
2023-03-20 14:02 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-03-20 14:30 ` Holger Jakobs <holger@jakobs.com>
2023-03-20 14:36 ` Thomas Kellerer <shammat@gmx.net>
2023-03-20 14:49 ` MichaelDBA <MichaelDBA@sqlexec.com>
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