Re: [GENERAL] Scripting function definitions as SQL?
On Mon, May 12, 2008 at 10:57 AM, Reece Hart [EMAIL PROTECTED] wrote: 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. 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, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Scripting function definitions as SQL?
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 [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Scripting function definitions as SQL?
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 [EMAIL PROTECTED] 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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Scripting function definitions as SQL?
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) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Scripting function definitions as SQL?
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 [EMAIL PROTECTED] 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) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Scripting function definitions as SQL?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
