Thread: Scripting function definitions as SQL?

Has anyone written a function that scripts out all the functions in a
database as full SQL statements (Create Function.....)

I found the below SQL will return all the fields needed to build a SQL
statement, but it would take some work to combine the field values
correctly to get the right format.  So does anyone know if the code
has already been written by someone else?


SELECT p.proname AS name, p.oid, p.proargtypes AS args, ds.description
, p.prorettype AS rettype,
 p.proretset, p.probin, p.proisstrict AS strict, p.prosrc AS body,
l.lanname AS lang,
 u.usename, p.prosecdef, p.provolatile, p.proisagg, n.nspname,
proargnames, p.proargmodes, p.proallargtypes
FROM pg_proc p
LEFT OUTER JOIN pg_description ds ON ds.objoid = p.oid
INNER JOIN pg_namespace n ON p.pronamespace = n.oid
INNER JOIN pg_language l ON l.oid = p.prolang
LEFT OUTER JOIN pg_user u ON u.usesysid = p.proowner
WHERE n.nspname = 'main'
ORDER BY p.proname, n.nspname

On Sun, May 11, 2008 at 06:12:40AM -0700, Postgres User wrote:
> Has anyone written a function that scripts out all the functions in a
> database as full SQL statements (Create Function.....)
>
> I found the below SQL will return all the fields needed to build a SQL
> statement, but it would take some work to combine the field values
> correctly to get the right format.  So does anyone know if the code
> has already been written by someone else?

Does pg_dump not do what you want?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Unfortunately I didn't see a way to tell pg_dump to dump only objects
of a specific type, like functions or sequences.  It requires
additional coding to parse the output and that's less than ideal...

>  Does pg_dump not do what you want?

On Sun, May 11, 2008 at 6:49 AM, Martijn van Oosterhout
<kleptog@svana.org> wrote:
> On Sun, May 11, 2008 at 06:12:40AM -0700, Postgres User wrote:
> > Has anyone written a function that scripts out all the functions in a
> > database as full SQL statements (Create Function.....)
> >
> > I found the below SQL will return all the fields needed to build a SQL
> > statement, but it would take some work to combine the field values
> > correctly to get the right format.  So does anyone know if the code
> > has already been written by someone else?

On Sun, May 11, 2008 at 11:28:37AM -0700, Postgres User wrote:
> Unfortunately I didn't see a way to tell pg_dump to dump only objects
> of a specific type, like functions or sequences.  It requires
> additional coding to parse the output and that's less than ideal...

hmmm .. "additional coding" seems a bit too much for a simple thing like
this:
pg_dump -s | perl -ne 'print if /^CREATE FUNCTION test_it/../^\s+LANGUAGE/'

of course it would be cool to have switch to do it, but hey - it hardly
even qualifies as one-liner. it's more "an expression" than code.

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

Yes, but I'm doing this from a Delphi program in Windows and that's
why I'm looking for a solution that's SQL-based.

It would be nice if one of the system catalog views handled it.

> hmmm .. "additional coding" seems a bit too much for a simple thing like
> this:
> pg_dump -s | perl -ne 'print if /^CREATE FUNCTION test_it/../^\s+LANGUAGE/'
>
> of course it would be cool to have switch to do it, but hey - it hardly
> even qualifies as one-liner. it's more "an expression" than code.

On Sun, May 11, 2008 at 11:43 AM, hubert depesz lubaczewski
<depesz@depesz.com> wrote:
> On Sun, May 11, 2008 at 11:28:37AM -0700, Postgres User wrote:
> > Unfortunately I didn't see a way to tell pg_dump to dump only objects
> > of a specific type, like functions or sequences.  It requires
> > additional coding to parse the output and that's less than ideal...
>
> hmmm .. "additional coding" seems a bit too much for a simple thing like
> this:
> pg_dump -s | perl -ne 'print if /^CREATE FUNCTION test_it/../^\s+LANGUAGE/'
>
> of course it would be cool to have switch to do it, but hey - it hardly
> even qualifies as one-liner. it's more "an expression" than code.
>
>
> --
> quicksil1er: "postgres is excellent, but like any DB it requires a
> highly paid DBA.  here's my CV!" :)
> http://www.depesz.com/ - blog dla ciebie (i moje CV)
>

On Sun, 2008-05-11 at 06:12 -0700, Postgres User wrote:
> Has anyone written a function that scripts out all the functions in a
> database as full SQL statements (Create Function.....)

You could pg_dump the schema in the "custom" format (-Fc), then call
pg_restore with -l to get the TOC, grep the TOC for functions, and feed
that back into pg_restore with -L. It sounds like a lot, but it's pretty
easy in practice, like so:

$ sudo -u postgres pg_dump -Fc -s mydb >mydb.pgdfc
$ pg_restore -l mydb.pgdfc >mydb.toc
$ grep -E '^[0-9]+; [0-9]+ [0-9]+ FUNCTION' mydb.toc >mydb-fx.toc
$ pg_restore -L mydb-fx.toc mydb.pgdfc

The output of pg_restore is sql.

This technique is extremely useful for other kinds of schema elements as
well.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

On Mon, May 12, 2008 at 10:57 AM, Reece Hart <reece@harts.net> wrote:

It's a bit too much for the task at hand. Recently I was also faced with a situation where I wanted to dump a few functions (only), but pg_dump does not have any option to do so!!

Can we have an option to dump function?

Best regards,

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device