Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E3923476660 for ; Wed, 2 Oct 2002 13:57:28 -0400 (EDT) Received: from hotmail.com (dav46.law15.hotmail.com [64.4.22.18]) by postgresql.org (Postfix) with ESMTP id 187964764D4 for ; Wed, 2 Oct 2002 13:57:28 -0400 (EDT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Wed, 2 Oct 2002 10:57:28 -0700 X-Originating-IP: [204.244.28.188] From: "david williams" To: , "Joe Conway" Cc: Subject: Re: Stored Procedures Date: Wed, 2 Oct 2002 13:57:27 -0400 MIME-Version: 1.0 X-Mailer: MSN Explorer 7.00.0021.1900 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0002_01C26A1B.A453C5D0" Message-ID: X-OriginalArrivalTime: 02 Oct 2002 17:57:28.0046 (UTC) FILETIME=[2BD0F8E0:01C26A3D] X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/26 X-Sequence-Number: 9744 ------=_NextPart_001_0002_01C26A1B.A453C5D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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=20=20 then what do I need to do if I am running 7.2.1? When I=20=20 try to use the SETOF to retrun a row set, I only get=20=20 one column. Do I need to update Postgres to get things to work? Ben > david williams wrote: > > Also, > >=20=20=20 > > the table definition MUST be in the Public Schema. I use my own schema= =20=20 > > names but in order for the table to be found by the function it ( the= =20=20 > > table ) must be in the public schema. Although it can be empty. >=20=20 > (Note: > this discussion does not apply to PostgreSQL releases prior to 7.3 beta) >=20=20 > Not true. You need to be sure the schema the table is in is in your searc= h=20=20 > path, or you need to fully qualify the table reference. See below for an= =20=20 > example: >=20=20 > -- create a new schema > CREATE SCHEMA s1; > CREATE SCHEMA > -- change to the new schema > SET search_path=3D's1','$user','public'; > SET > select current_schema(); > current_schema > ---------------- > s1 > (1 row) >=20=20 > -- 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=3D'$user','public','s1'; > SET > select current_schema(); > current_schema > ---------------- > public > (1 row) >=20=20 > \dt > List of relations > Schema | Name | Type | Owner > --------+------+-------+---------- > s1 | foo | table | postgres > (1 row) >=20=20 > CREATE FUNCTION getfoo(int) RETURNS foo AS ' > SELECT * FROM foo WHERE fooid =3D $1; > ' LANGUAGE SQL; > CREATE FUNCTION > \df getfoo > List of functions > Result data type | Schema | Name | Argument data types > ------------------+--------+--------+--------------------- > foo | public | getfoo | integer > (1 row) >=20=20 > -- this will work > SELECT *, upper(fooname) FROM getfoo(1) AS t1; > fooid | foosubid | fooname | upper > -------+----------+---------+------- > 1 | 1 | Joe | JOE > (1 row) >=20=20 > -- 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=3D'$user','public'; > SET > select current_schema(); > current_schema > ---------------- > public > (1 row) >=20=20 > \dt > No relations found. > CREATE FUNCTION getfoo(int) RETURNS s1.foo AS ' > SELECT * FROM s1.foo WHERE fooid =3D $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) >=20=20 > -- this will work > SELECT *, upper(fooname) FROM getfoo(1) AS t1; > fooid | foosubid | fooname | upper > -------+----------+---------+------- > 1 | 1 | Joe | JOE > (1 row) >=20=20 > HTH, >=20=20 > Joe > Get more from the Web. FREE MSN Explorer download : http://explorer.msn.= com ------=_NextPart_001_0002_01C26A1B.A453C5D0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Stored procedure= s 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 colu= mn in the returns area of the function.
 
Dave
 
----- Original Message -----
From: = bcschnei@attbi.com
Sent: Wedne= sday, October 02, 2002 1:53 PM
To:= Joe Conway
Cc: david will= iams; pgsql-sql@postgresql.org
Sub= ject: Re: [SQL] Stored Procedures
 
Ok, if this do= es 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 Schem= a. 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:
> &nb= sp; 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 re= ference. See below for an
> example:
>
> -- create a ne= w schema
> CREATE SCHEMA s1;
> CREATE SCHEMA
> -- change = to the new schema
> SET search_path=3D's1','$user','public';
> = SET
> select current_schema();
>   current_schema
= > ----------------
>   s1
> (1 row)
>
&g= t; -- create the table
> CREATE TABLE foo (fooid int, foosubid int, f= ooname text);
> CREATE TABLE
> INSERT INTO foo VALUES(1,1,'Joe'= );
> INSERT 794076 1
> -- change back to public schema, but lea= ve s1 in the search path
> SET search_path=3D'$user','public','s1';> SET
> select current_schema();
>   current_sch= ema
> ----------------
>   public
> (1 row)
= >
> \dt
>        &n= bsp; List of relations
>   Schema | Name | Type  |&nbs= p; Owner
> --------+------+-------+----------
>   s1&= nbsp;    | foo  | table | postgres
> (1 row)
&= gt;
> CREATE FUNCTION getfoo(int) RETURNS foo AS '
> &nbs= p;  SELECT * FROM foo WHERE fooid =3D $1;
> ' LANGUAGE SQL;
&= gt; CREATE FUNCTION
> \df getfoo
>     = ;            &n= bsp;    List of functions
>   Result data ty= pe | Schema |  Name  | Argument data types
> --------------= ----+--------+--------+---------------------
>   foo &= nbsp;            | p= ublic | getfoo | integer
> (1 row)
>
> -- this will work=
> SELECT *, upper(fooname) FROM getfoo(1) AS t1;
>  = fooid | foosubid | fooname | upper
> -------+----------+---------+--= -----
>       1 |   &nbs= p;    1 | Joe     | JOE
> (1 row)<= BR>>
> -- 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=3D'$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 =3D $1;
> ' LANGUAGE SQL;
> CREATE FUNCTION
> \df g= etfoo
>          &n= bsp;           List of fu= nctions
>   Result data type | Schema |  Name  | = Argument data types
> ------------------+--------+--------+----------= -----------
>   s1.foo      &= nbsp;    | public | getfoo | integer
> (1 row)
>=
> -- this will work
> SELECT *, upper(fooname) FROM getfoo(1)= AS t1;
>   fooid | foosubid | fooname | upper
> ----= ---+----------+---------+-------
>      = ; 1 |        1 | Joe   &n= bsp; | JOE
> (1 row)
>
> HTH,
>
> Joe
&g= t;

Get more from the Web= . FREE MSN Explorer download : http://= explorer.msn.com

------=_NextPart_001_0002_01C26A1B.A453C5D0--