Dumping functions with pg_dump
Hello,
Is it possible yet in 8.4 to pg_dump specific functions without having to do
the whole pg_restore thing?
If it is possible, what is the syntax to dump a specific function?
If not possible, then how does one use pg_restore to target a specific
function?
thx
On 06/04/2011 00:15, Greg Corradini wrote:
Hello,
Is it possible yet in 8.4 to pg_dump specific functions without having
to do the whole pg_restore thing?
If I understand correctly what you're trying to do, a handy alternative
is to use pgAdmin, right click on the function in the tree view, and
select Scripts -> Create.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
Thanks for the reply Raymond!
This is all through remote terminal so I can't use pg_admin ;(
Maybe some more quick context.... I don't want to dump whole database b/c
the thing is 12GB and for the application we're building we only access
certain tables in the DB. There's one table that has two triggers associated
to it, each which in turn references it's own procedure.
So my pg_dump syntax has a lot of -t <table_name> -t<table_nameII> in it to
target only the tables I want. But the procedures tied to the triggers
aren't coming with it unless I do the whole dump.
thx
On Tue, Apr 5, 2011 at 4:19 PM, Raymond O'Donnell <rod@iol.ie> wrote:
On 06/04/2011 00:15, Greg Corradini wrote:
Hello,
Is it possible yet in 8.4 to pg_dump specific functions without having
to do the whole pg_restore thing?If I understand correctly what you're trying to do, a handy alternative is
to use pgAdmin, right click on the function in the tree view, and select
Scripts -> Create.Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
--
Greg
Greg Corradini <gregcorradini@gmail.com> writes:
Hello,
Is it possible yet in 8.4 to pg_dump specific functions without having to do
the whole pg_restore thing?If it is possible, what is the syntax to dump a specific function?
If not possible, then how does one use pg_restore to target a specific
function?
Just do;
pg_dump --schema-only
Go find the function definition in the output script, snip it out and
load with psql. Remember to load it into the correct schema and
whatever other details.
HTH
--
Jerry Sievers
e: gsievers19@comcast.net
p: 305.321.1144
On Tuesday, April 05, 2011 4:19:56 pm Raymond O'Donnell wrote:
On 06/04/2011 00:15, Greg Corradini wrote:
Hello,
Is it possible yet in 8.4 to pg_dump specific functions without having
to do the whole pg_restore thing?If I understand correctly what you're trying to do, a handy alternative
is to use pgAdmin, right click on the function in the tree view, and
select Scripts -> Create.Ray.
In addition, from inside psql do \ef function_name. This opens the function in
an editor from which you can save it elsewhere.
If you are talking a lot of functions then you can use the -l and -L options to
pg_restore to create a TOC list that can be edited to contain only the functions
you want. These than can either be restored to a database or file.
--
Adrian Klaver
adrian.klaver@gmail.com
On Tue, Apr 5, 2011 at 4:30 PM, Adrian Klaver <adrian.klaver@gmail.com>wrote:
On Tuesday, April 05, 2011 4:19:56 pm Raymond O'Donnell wrote:
On 06/04/2011 00:15, Greg Corradini wrote:
Hello,
Is it possible yet in 8.4 to pg_dump specific functions without having
to do the whole pg_restore thing?If I understand correctly what you're trying to do, a handy alternative
is to use pgAdmin, right click on the function in the tree view, and
select Scripts -> Create.Ray.
In addition, from inside psql do \ef function_name. This opens the function
in
an editor from which you can save it elsewhere.
If you are talking a lot of functions then you can use the -l and -L
options to
pg_restore to create a TOC list that can be edited to contain only the
functions
you want. These than can either be restored to a database or file.--
Adrian Klaver
adrian.klaver@gmail.com
Thx for the replies Adrian and Jerry,
Those are both options. Jerry, your suggestion is the work around I've
already used. Adrian, I did not know you could do that. Still...I was
looking for something that worked inline with pg_dump...and it looks like
pg_restore is still the major game in town.
It would be nice if pg_dump got some option flags to do this sort of thing
(though I'm naive on why this doesn't exist in the first place)
On Tuesday, April 05, 2011 5:24:13 pm Greg Corradini wrote:
On Tue, Apr 5, 2011 at 4:30 PM, Adrian Klaver <adrian.klaver@gmail.com>wrote:
Thx for the replies Adrian and Jerry,
Those are both options. Jerry, your suggestion is the work around I've
already used. Adrian, I did not know you could do that. Still...I was
looking for something that worked inline with pg_dump...and it looks like
pg_restore is still the major game in town.
Yea, the other common method is to develop from the outside in, instead of
inside out. To explain, outside in would be to keep the schema object creation
scripts in files external to the database and feed them to the database as
needed. Initial object creation and revisions are done on the external files.
Inside out would be what you are doing, pulling the schema files from inside the
database. One is not necessarily better than the other, just each has its
strengths and weaknesses, as you are finding:)
It would be nice if pg_dump got some option flags to do this sort of thing
(though I'm naive on why this doesn't exist in the first place)
pg_dump/pg_restore has become more flexible over the years, but there are still
dependency issues between schema objects that make what you want difficult. The
dependency tracking really only fully works for a complete dump/restore.
--
Adrian Klaver
adrian.klaver@gmail.com
On Apr 6, 2011, at 4:45 AM, Greg Corradini wrote:
Hello,
Is it possible yet in 8.4 to pg_dump specific functions without having to do the whole pg_restore thing?If it is possible, what is the syntax to dump a specific function?
If not possible, then how does one use pg_restore to target a specific function?
thx
Not from pg_dump. You can use psql to dump the specific functions using pg_get_functiondef(oid)
psql -c "select pg_get_functiondef(oid) from pg_proc where proname='<fnction name>' and pronamespace=(select oid from pg_namespace where nspname='schemaname');" <dbname>;
Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com