Thread: table dump function
I need a pl/pgsql function that will dump a table ddl so I can export the definition to a file before I drop the object.
the psql \d command won’t work, since it doesn’t dump the table ddl, it just lists the table’s attributes and indexes.
I’ve got the function mostly working, but need help translating the ACL list into the grant commands.
Are there any standard PG functions that would be useful for creating the grants?
Thanks in advance.
Doug Little
Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz Worldwide
500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741
orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com
Attachment
On 03/07/2013 07:58 AM, Little, Douglas wrote: > I need a pl/pgsql function that will dump a table ddl so I can export > the definition to a file before I drop the object. > > the psql \d command won’t work, since it doesn’t dump the table ddl, it > just lists the table’s attributes and indexes. > > I’ve got the function mostly working, but need help translating the ACL > list into the grant commands. > > Are there any standard PG functions that would be useful for creating > the grants? pg_dump -U some_user -t some_table -s -f some_table.sql some_database http://www.postgresql.org/docs/9.2/interactive/app-pgdump.html > > Thanks in advance. > > *Doug Little* -- Adrian Klaver adrian.klaver@gmail.com
Thanks for the suggestion Adrian, Unfortunately, my process is executing from psql, so to start pg_dump from within psql is a bit challenging. We have 1 instance where we start an OS process from a function, but it's new territory for us. My function is dumping everything but the ACL grant list. I currently have a function that will build and execute thegrants from the acl list, so it's just a simple exercise to get it to dump the statements. I was just hoping that there were standard functions for doing this. Thanks doug
On 03/07/2013 09:42 AM, Little, Douglas wrote: > Thanks for the suggestion Adrian, > > Unfortunately, my process is executing from psql, so to start pg_dump from within psql is a bit challenging. > We have 1 instance where we start an OS process from a function, but it's new territory for us. > > My function is dumping everything but the ACL grant list. I currently have a function that will build and execute thegrants from the acl list, > so it's just a simple exercise to get it to dump the statements. > > I was just hoping that there were standard functions for doing this. > Thanks > doug > \! pg_dump... perhaps?
On 03/07/2013 08:42 AM, Little, Douglas wrote: > Thanks for the suggestion Adrian, > > Unfortunately, my process is executing from psql, so to start pg_dump from within psql is a bit challenging. > We have 1 instance where we start an OS process from a function, but it's new territory for us. Yeah, my suggestion was offered in the spirit of not reinventing the wheel:) When you say you are running from inside psql, do you mean interactively or from script? As Rob mentioned from psql you can get to the shell by doing \!. > > My function is dumping everything but the ACL grant list. I currently have a function that will build and execute thegrants from the acl list, > so it's just a simple exercise to get it to dump the statements. > > I was just hoping that there were standard functions for doing this. Not that I know of. If you feel adventurous you could look at dumpTableSchema in pg_dump.c in the source to see how pg_dump does it. > Thanks > doug > -- Adrian Klaver adrian.klaver@gmail.com
Adrian, Thank you for your comments. This is part of our deployment framework. DDL files are submitted to the framework, which eventually run as psql scripts. thanks for the tip about pg_dump.c I'll take a look. I'll also look at escaping out from psql. Thanks -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@gmail.com] Sent: Thursday, March 07, 2013 12:19 PM To: Little, Douglas Cc: PostgreSQL General (pgsql-general@postgresql.org) Subject: Re: [GENERAL] table dump function On 03/07/2013 08:42 AM, Little, Douglas wrote: > Thanks for the suggestion Adrian, > > Unfortunately, my process is executing from psql, so to start pg_dump from within psql is a bit challenging. > We have 1 instance where we start an OS process from a function, but it's new territory for us. Yeah, my suggestion was offered in the spirit of not reinventing the wheel:) When you say you are running from inside psql, do you mean interactively or from script? As Rob mentioned from psqlyou can get to the shell by doing \!. > > My function is dumping everything but the ACL grant list. I currently have a function that will build and execute thegrants from the acl list, > so it's just a simple exercise to get it to dump the statements. > > I was just hoping that there were standard functions for doing this. Not that I know of. If you feel adventurous you could look at dumpTableSchema in pg_dump.c in the source to see how pg_dumpdoes it. > Thanks > doug > -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver wrote on 07.03.2013 19:18: > Not that I know of. If you feel adventurous you could look at > dumpTableSchema in pg_dump.c in the source to see how pg_dump does > it. I think a function "pg_get_tabledef()" would be very helpful. We already have a lot of pg_get_XXXdef() functions (pg_get_viewdef, pg_get_functiondef, ...) I would make sense to have one for tables as well. Regards Thomas
On 03/07/2013 11:09 AM, Little, Douglas wrote: > Adrian, > Thank you for your comments. > This is part of our deployment framework. DDL files are submitted to the framework, which eventually run as psql scripts. Just out of curiosity, if you are creating the DDL, do you not already have the schema/grant information from the previous runs? > > thanks for the tip about pg_dump.c I'll take a look. > I'll also look at escaping out from psql. > > Thanks > -- Adrian Klaver adrian.klaver@gmail.com