Thread: How to dump JUST procedures/funnctions?
I'd like to dump to text the full SQL required to create/replace all user-defined functions within a specific schema - but JUST the function declarations. We are doing server migration and there are some network paths in the code I would like to search and replace. Carlo
Carlo Stonebanks <stonec.register@sympatico.ca> wrote:
> I'd like to dump to text the full SQL required to create/replace all
> user-defined functions within a specific schema - but JUST the function
> declarations.
>
> We are doing server migration and there are some network paths in the
> code I would like to search and replace.
All functions are stored in pg_catalog.pg_proc, you can search the
column prosrc for your network paths.
And you can get the whole function-definition with pg_get_functiondef.
Okay. Now you can run this select:
select 'select pg_get_functiondef (' || oid || ');' from pg_proc where prosrc ~ 'network path';
The result can you use to run as commands to retrieve all
function-definitions.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Is pg_get_functiondef an 8.4 appears to be an 8.4 function?
I don't see it in the 8.3 documentation and the servers in question are all
8.3.
Any alternatives for 8.3? pg_proc has the code body, but not the function
declaration, etc.
"Andreas Kretschmer" <akretschmer@spamfence.net> wrote in message
news:20100320081646.GA26868@tux...
> Carlo Stonebanks <stonec.register@sympatico.ca> wrote:
>
>> I'd like to dump to text the full SQL required to create/replace all
>> user-defined functions within a specific schema - but JUST the function
>> declarations.
>>
>> We are doing server migration and there are some network paths in the
>> code I would like to search and replace.
>
> All functions are stored in pg_catalog.pg_proc, you can search the
> column prosrc for your network paths.
>
> And you can get the whole function-definition with pg_get_functiondef.
>
>
> Okay. Now you can run this select:
>
> select 'select pg_get_functiondef (' || oid || ');' from pg_proc where
> prosrc ~ 'network path';
>
> The result can you use to run as commands to retrieve all
> function-definitions.
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknown)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
In response to Carlo Stonebanks : > Is pg_get_functiondef an 8.4 appears to be an 8.4 function? Yes, new since 8.4. > > I don't see it in the 8.3 documentation and the servers in question are all > 8.3. > > Any alternatives for 8.3? pg_proc has the code body, but not the function > declaration, etc. Afaik no, you can make a schema-dump and extract the function declarations from the dump. Or, upgrade to 8.4 ;-) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
> Afaik no, you can make a schema-dump and extract the function > declarations from the dump. Yeah, that's what I was doing. Bloody tedious. Thanks anyway!
"Carlo Stonebanks" <stonec.register@sympatico.ca> writes:
>> Afaik no, you can make a schema-dump and extract the function
>> declarations from the dump.
> Yeah, that's what I was doing. Bloody tedious. Thanks anyway!
It seems like it could be automated.
pg_dump -Fc -s mydb >mydb.dump
pg_restore -l mydb.dump | grep FUNCTION >mydb.list
pg_restore -L mydb.list mydb.dump >functionsonly.txt
(or something more or less like that --- too early in the morning...)
regards, tom lane
On Sat, Mar 20, 2010 at 11:09 PM, Carlo Stonebanks <stonec.register@sympatico.ca> wrote:
Is pg_get_functiondef an 8.4 appears to be an 8.4 function?
I don't see it in the 8.3 documentation and the servers in question are all 8.3.
Any alternatives for 8.3? pg_proc has the code body, but not the function declaration, etc.
I've attached a simple perl script to dump all functions per schema from a database. I won't claim this is perfect, but it does the trick for me.
Works with 8.3. If you have 2 functions called public.foo, it will put them in public/foo.sql with CREATE OR REPLACE FUNCTION formatting around it.
Hope this is useful to others.
Tony Wasson
Attachment
Hi Tony,
Thanks VERY much! That got me on my way. I'm going to try and convert this to a PG stored function, Since you know that the script has limitations, you or othres reading might want to know the ones I've found:
1) Regardless of what you set the schema to, the only ones that are dumped are those on the search path due to pg_catalog.pg_function_is_visible(p.oid)
2) COST and ROWS are lost3) Declared parameter names are lost
4) IN, OUT, etc parameter direction modes are lost
5) If any parameter mode other than IN is declared, they are lost -- you'd need pg_catalog.oidvectortypes(array_to_string(p.proallargtypes, ' ')::oidvector) in this case ONLY.
6) The script does not generate any winning lottery numbers
2) COST and ROWS are lost3) Declared parameter names are lost
4) IN, OUT, etc parameter direction modes are lost
5) If any parameter mode other than IN is declared, they are lost -- you'd need pg_catalog.oidvectortypes(array_to_string(p.proallargtypes, ' ')::oidvector) in this case ONLY.
6) The script does not generate any winning lottery numbers
Carlo
"Tony Wasson" <ajwasson@gmail.com> wrote in message news:6d8daee31003221657h53f486d7r164b27587415e8c6@mail.gmail.com...On Sat, Mar 20, 2010 at 11:09 PM, Carlo Stonebanks <stonec.register@sympatico.ca> wrote:Is pg_get_functiondef an 8.4 appears to be an 8.4 function?
I don't see it in the 8.3 documentation and the servers in question are all 8.3.
Any alternatives for 8.3? pg_proc has the code body, but not the function declaration, etc.
I've attached a simple perl script to dump all functions per schema from a database. I won't claim this is perfect, but it does the trick for me.
Works with 8.3. If you have 2 functions called public.foo, it will put them in public/foo.sql with CREATE OR REPLACE FUNCTION formatting around it.
Hope this is useful to others.
Tony Wasson
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general