Thread: Dump only functions...

Cristian Prieto <cristian@clickdiario.com> schrieb:

> Any of you knows is there is any way in pg_dump or anything to dump just the
> functions from a database?

Net really a solution, but your defined functions are stored in
information_schema.routines


HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Am Dienstag, den 18.10.2005, 15:31 -0600 schrieb Cristian Prieto:
> Any of you knows is there is any way in pg_dump or anything to dump
> just the functions from a database?

pg_dump -Fc -v -f temp.dump yourdatabase
pg_restore -l temp.dump | grep FUNCTION >functionlist
pg_restore -L functionlist temp.dump >yourfunctions.sql

of course you can just use your regular dump and so
skip the first part.

HTH
Tino

I'm not sure of your intentions but just as a suggestion, download
PGADMIN III. You can select a SCHEMA and do a BACKUP with many options
for your custom editing. (I'm new and that's the fastest way if you
have hundreds of functions & procedures like me) you can tweek the
generated backup file and reinsert what you need...

Just my 2 cents
Troy

Tino Wildenhain wrote:
> Am Dienstag, den 18.10.2005, 15:31 -0600 schrieb Cristian Prieto:
>
>>Any of you knows is there is any way in pg_dump or anything to dump
>>just the functions from a database?
>
>
> pg_dump -Fc -v -f temp.dump yourdatabase
> pg_restore -l temp.dump | grep FUNCTION >functionlist
> pg_restore -L functionlist temp.dump >yourfunctions.sql
>
> of course you can just use your regular dump and so
> skip the first part.
>
> HTH
> Tino


Another way of dumping only the functions, based off the view pga_functions:

-- view definition
create or replace view pga_functions as
select
    l.lanname as language,
    n.nspname||'.'||p.proname||'('
||pg_catalog.oidvectortypes(p.proargtypes)||')' as name,
    t.typname as returntype,
    '\n\n'||'CREATE OR REPLACE FUNCTION
'||n.nspname||'.'||p.proname||'('||pg_catalog.oidvectortypes(p.proargtypes)||')\n'||
   '  RETURNS '||t.typname||' AS'||'\n''\n' ||(select case when lanname <> 'c'
then  replace(prosrc,'\'','\\\'') else replace(prosrc,'\'','\\\'')||'.so'
end)||'\n''\n'||' LANGUAGE ''' || l.lanname || ''' VOLATILE;\n' as source
from pg_proc p, pg_type t, pg_namespace n, pg_language l
where p.prorettype = t.oid and p.pronamespace = n.oid
and p.prolang = l.oid;


dev=# select source from pga_functions where name like 'public%'
dev-# \o dump_all_functions.sql;

This will dump public's functions to a file. You may want to play with the
settings of view etc to get the formatted results you want.


Note: I did not create this view, I found it in the archives a while back, along
with pga_objects, pga_columns, and pga_views.


Cheers,

Bricklen
--
_______________________________

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________