Re: 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: bcschnei(at)attbi(dot)com
Sent: Wednesday, October 02, 2002 1:53 PM
To: Joe Conway
Cc: david williams; pgsql-sql(at)postgresql(dot)org
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