Re: How to dump JUST procedures/funnctions?

From: "Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: How to dump JUST procedures/funnctions?
Date: 2010-03-24 22:00:59
Message-ID: hoe22n$1oh2$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Carlo
"Tony Wasson" <ajwasson(at)gmail(dot)com> wrote in message news:6d8daee31003221657h53f486d7r164b27587415e8c6(at)mail(dot)gmail(dot)com(dot)(dot)(dot)
On Sat, Mar 20, 2010 at 11:09 PM, Carlo Stonebanks <stonec(dot)register(at)sympatico(dot)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(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Karina Guardado 2010-03-24 22:01:48 Re: data type to store a lot of text and tables of data
Previous Message Raymond O'Donnell 2010-03-24 21:55:06 Re: data type to store a lot of text and tables of data