Re: [SQL] Returning a reference to a cursor from a function
Thanks again Richard. I did find that dev note and I do have a version of this working but of course it does not return up to the ASP layer. Since I need to integrate Postgresql ( or something else ) into an existing application using COM as the middle and ASP as the upper layer I must create a function similar to how MS SQL Server handles it. Ah well Thankgs again for you help and I will keep watch on 7.3. Do you know if the ODBC Driver will also be updated to accomodate this function or is the DECLARE/FETCH setting enough. Thanks Dave - Original Message - From: Richard Huxton Sent: Wednesday, September 18, 2002 6:32 AM To: david williams Cc: [EMAIL PROTECTED] Subject: Re: [SQL] Returning a reference to a cursor from a function On Tuesday 17 Sep 2002 7:12 pm, you wrote:> Richard,>> Thanks for the information. I've made some modifications to your code here> so that it does a RAISE NOTICE in each loop returning simply the value of n> and then when the loop is finished it again returns n.>> This works fine at the psql level but after it passes through ODBC to the> ASP layer all I get is the final RETURN value.Yep - the NOTICE is really a type of error message (you can use RAISE to generate errors too) and isn't part of your data-stream.> I have tried using the RETURN function in the loop but it terminates the> loop.Indeed it does.> I really need to return each record up to the ASP layer.The solution to this sort of thing in version 7.3 is something called table functions, but I think they're limited to C at the moment, not plpgsql.With 7.2 you need to return the cursor from the function and then FETCH from it. An example was missed out from the 7.2.1 docs but you can see one in the developer's docs (bottom of page):http://developer.postgresql.org/docs/postgres/plpgsql-cursors.htmlTo hack our example a bit more the below takes a cursor-name and table name and defines a cursor for you.Note that when using it, you need to be within BEGIN...COMMIT (an explicit transaction) since the cursor returned from the function only lasts until the end of a transaction.HTH- Richard HuxtonDROP FUNCTION foo_count(refcursor, text);CREATE FUNCTION foo_count(refcursor, text) RETURNS refcursor AS 'DECLARE curs ALIAS FOR $1; tbl_name ALIAS FOR $2;BEGIN RAISE NOTICE ''cursor on table: %'',tbl_name; OPEN curs FOR EXECUTE ''SELECT * FROM '' || tbl_name; RETURN curs;END;'language 'plpgsql';richardh=> BEGIN;BEGINrichardh=> SELECT foo_count('fake_cursor','companies');NOTICE: cursor on table: companies foo_count-fake_cursor(1 row)richardh=> FETCH 3 FROM fake_cursor;co_id | co_name | co_postcode | co_lastchg---+--+-+--- 56 | Acme Associates GmBH | unknown | 2002-06-12 14:04:43.123408+01 57 | Imperial Investments Inc | unknown | 2002-06-12 14:04:43.123408+01 58 | Universal Associates USA | unknown | 2002-06-12 14:04:43.123408+01(3 rows)richardh=> COMMIT;---(end of broadcast)---TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com
Re: [SQL] Stored Procedures
Stored procedures returning more than one row up through odbc does not work in 7.2.1 To return more than one column you must spec is column in the returns area of the function. Dave - Original Message - From: [EMAIL PROTECTED] Sent: Wednesday, October 02, 2002 1:53 PM To: Joe Conway Cc: david williams; [EMAIL PROTECTED] Subject: Re: [SQL] Stored Procedures Ok, if this does not apply to versions prior to 7.3beta then what do I need to do if I am running 7.2.1? When I try to use the SETOF to retrun a row set, I only get one column.Do I need to update Postgres to get things to work?Ben> david williams wrote:> > Also,> > > > the table definition MUST be in the Public Schema. I use my own schema > > names but in order for the table to be found by the function it ( the > > table ) must be in the public schema. Although it can be empty.> > (Note:> this discussion does not apply to PostgreSQL releases prior to 7.3 beta)> > Not true. You need to be sure the schema the table is in is in your search > path, or you need to fully qualify the table reference. See below for an > example:> > -- create a new schema> CREATE SCHEMA s1;> CREATE SCHEMA> -- change to the new schema> SET search_path='s1','$user','public';> SET> select current_schema();> current_schema> > s1> (1 row)> > -- create the table> CREATE TABLE foo (fooid int, foosubid int, fooname text);> CREATE TABLE> INSERT INTO foo VALUES(1,1,'Joe');> INSERT 794076 1> -- change back to public schema, but leave s1 in the search path> SET search_path='$user','public','s1';> SET> select current_schema();> current_schema> > public> (1 row)> > \dt> List of relations> Schema | Name | Type | Owner> +--+---+--> s1 | foo | table | postgres> (1 row)> > CREATE FUNCTION getfoo(int) RETURNS foo AS '> SELECT * FROM foo WHERE fooid = $1;> ' LANGUAGE SQL;> CREATE FUNCTION> \df getfoo> List of functions> Result data type | Schema | Name | Argument data types> --+++-> foo | public | getfoo | integer> (1 row)> > -- this will work> SELECT *, upper(fooname) FROM getfoo(1) AS t1;> fooid | foosubid | fooname | upper> ---+--+-+---> 1 | 1 | Joe | JOE> (1 row)> > -- now try again with table name qualified in the function> DROP FUNCTION getfoo(int);> DROP FUNCTION> -- remove s1 from the search path> SET search_path='$user','public';> SET> select current_schema();> current_schema> > public> (1 row)> > \dt> No relations found.> CREATE FUNCTION getfoo(int) RETURNS s1.foo AS '> SELECT * FROM s1.foo WHERE fooid = $1;> ' LANGUAGE SQL;> CREATE FUNCTION> \df getfoo> List of functions> Result data type | Schema | Name | Argument data types> --+++-> s1.foo | public | getfoo | integer> (1 row)> > -- this will work> SELECT *, upper(fooname) FROM getfoo(1) AS t1;> fooid | foosubid | fooname | upper> ---+--+-+---> 1 | 1 | Joe | JOE> (1 row)> > HTH,> > Joe> Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com
Re: [SQL] Stored Procedures
http://developer.postgresql.org/docs/postgres/xfunc-sql.html#AEN30400 See section 9.2.4. SQL Table Functions - Original Message - From: [EMAIL PROTECTED] Sent: Tuesday, October 01, 2002 4:25 PM To: [EMAIL PROTECTED] Subject: [SQL] Stored Procedures Hi all. I'm looking for a little help here. I have a project where I have to write some stored proceedures and am having some problems. My main issue is, I cannot figure out how to return a record set containing multipule columns. I am looking for a few examples on how I can do this. Most of what I have to do is fairly simple SQL queries based on a pramater sent to the function. I tried to use the SETOF option, but only get back one column.Any help will be would be greatly appricated. Simple examples would be of a great help.Thanks,Ben---(end of broadcast)---TIP 6: Have you searched our list archives?http://archives.postgresql.orgGet more from the Web. FREE MSN Explorer download : http://explorer.msn.com
Re: [SQL] Stored Procedures
Also, the table definition MUST be in the Public Schema. I use my own schema names but in order for the table to be found by the function it ( the table ) must be in the public schema. Although it can be empty. DaveGet more from the Web. FREE MSN Explorer download : http://explorer.msn.com
[SQL] unsubscribe
unsubscribe _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
