Re: Stored Procedures

----- Original Message -----

From: bcschnei@attbi.com

Sent: Wednesday, October 02, 2002 1:53 PM

To: Joe Conway

Cc: david williams; pgsql-sql@postgresql.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
>