Thread: 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




--
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

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_namespacewhere nspname='schemaname');" <dbname>; 


Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com