Re: 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
>