agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: david williams <dw_remote@hotmail.com>
To: bcschnei@attbi.com
To: Joe Conway <mail@joeconway.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Stored Procedures
Date: Wed, 2 Oct 2002 13:57:27 -0400
Message-ID: <DAV46GB35ELCSwpaXHH000091a9@hotmail.com> (raw)

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@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
> Get more from the Web.  FREE MSN Explorer download : http://explorer.msn.com

view thread (23+ messages)  latest in thread

Message-ID: <DAV46GB35ELCSwpaXHH000091a9@hotmail.com>
Permalink:  ../DAV46GB35ELCSwpaXHH000091a9@hotmail.com/
Also on:    postgresql.org/message-id/DAV46GB35ELCSwpaXHH000091a9@hotmail.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: dw_remote@hotmail.com, bcschnei@attbi.com, mail@joeconway.com
  Subject: Re: Stored Procedures
  In-Reply-To: <DAV46GB35ELCSwpaXHH000091a9@hotmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox