[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
[GENERAL] How to create a function with multiple RefCursor OUT parameters
I have the following function: CREATE OR REPLACE FUNCTION test_refcursor(INOUT tcount integer, OUT o_user refcursor, OUT o_name refcursor) RETURNS record AS $BODY$ BEGIN tcount := tcount + 1; OPEN o_user FOR SELECT * FROM user_table; OPEN o_name FOR SELECT * FROM name_table; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE Question 1: The function is not working with Npgsql .NET data provider. It did not return a valid .NET DataSet. But the INOUT parameter tcount works fine. How could I test the above function with SQL in pgAdmin III? I want to find out if problem is in the function or in the Npgsql. Question 2: pgAdmin III automatically added "RETURNS record" in the above function when RETURNS clause is not specified initially. Why is that? Is this the problem since it returns only single data table with the following value? How to fix it? tcount o_user o_name 23 Question 3: I want to return a single DataSet with each OUT RefCursor map to a DataTable within the DataSet, plus extra OUT parameters for individual OUT values. How could I create such a function? Any help is appreciated. -- 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
[GENERAL] Making sure \timing is on
Is there a way to make certain \timing is on? I am looking for something simmilar to what one can do with the pager \pset pager always Most scripts I am working with (inheritted) don't have \timing in them so I can put timing on/off as needed. However some scripts already have it. So if I set \timing before the script and the script calls it again then it gets turned off. As I go over all the scripts it will be less of an issue, but I still would like a way to make sure timing is on, before I run some scripts without having to first check the script to see if I had put \timing in the script or not. -- 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] Making sure \timing is on
Francisco Reyes <[EMAIL PROTECTED]> writes: > Is there a way to make certain \timing is on? Not that I know of :-(. There's been discussion of fixing all of psql's "toggle" commands to offer "\foo on" and "\foo off" variants, which would be far more scripting-friendly ... but nobody's stepped up to do it. regards, tom lane -- 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] Making sure \timing is on
Tom Lane writes: Not that I know of :-(. There's been discussion of fixing all of psql's "toggle" commands to offer "\foo on" and "\foo off" What would be a good starting point for someone interesting in looking into working on that? Started a job recently as PostgresSQL DBA.. so sooner or later I will want to start looking at the code. This may be one way to get my feet wet with the code. -- 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] Making sure \timing is on
Francisco Reyes <[EMAIL PROTECTED]> writes: > Tom Lane writes: >> Not that I know of :-(. There's been discussion of fixing all of psql's >> "toggle" commands to offer "\foo on" and "\foo off" > What would be a good starting point for someone interesting in looking into > working on that? This'd be a fine starting project IMHO, if you can read/code C at all. The rubber meets the road in exec_command() in src/bin/psql/command.c --- look around in that area. In particular note that ParseVariableBool in variables.c probably ought to be what you use to interpret the meaning of the argument. regards, tom lane -- 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] Making sure \timing is on
On Sun, May 11, 2008 at 9:04 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > Francisco Reyes <[EMAIL PROTECTED]> writes: >> Tom Lane writes: >>> Not that I know of :-(. There's been discussion of fixing all of psql's >>> "toggle" commands to offer "\foo on" and "\foo off" > >> What would be a good starting point for someone interesting in looking into >> working on that? > > This'd be a fine starting project IMHO, if you can read/code C at all. > The rubber meets the road in exec_command() in src/bin/psql/command.c > --- look around in that area. In particular note that ParseVariableBool > in variables.c probably ought to be what you use to interpret the > meaning of the argument. Is it reasonable behavior to have \timing along toggle and \timing on / \timing off be a forced switch? Just thinking of other scripts where this isn't a problem and having to update them. -- 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] Making sure \timing is on
"Scott Marlowe" <[EMAIL PROTECTED]> writes: > Is it reasonable behavior to have \timing along toggle and \timing on > / \timing off be a forced switch? Just thinking of other scripts > where this isn't a problem and having to update them. The command without an argument should certainly keep the old toggle behavior, for backwards compatibility. regards, tom lane -- 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
