Date: Thu, 2 Apr 2009 22:20:11 +0000 (UTC)
From: Adrian Klaver <aklaver@comcast.net>
To: peterw@borstad.com
Cc: pgsql-sql@postgresql.org
Message-ID: 
 <1521645855.2691471238710811603.JavaMail.root@sz0030a.emeryville.ca.mail.comcast.net>
In-Reply-To: <49D4E354.9090106@borstad.com>
Subject: Re: FUNCTION problem
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


----- "Peter Willis" <peterw@borstad.com> wrote:

> Adrian Klaver wrote:
> > On Wednesday 01 April 2009 4:31:20 pm Peter Willis wrote:
> >> Hello,
> >>
> >> I am having a problem with a FUNCTION.
> >> The function creates just fine with no errors.
> >>
> >> However, when I call the function postgres produces an error.
> >>
> >> Perhaps someone can enlighten me.
> >>
> >>
> >> --I can reproduce the error by making a test function
> >> --that is much easier to follow that the original:
> >>
> >> CREATE OR REPLACE FUNCTION test_function(integer)
> >>    RETURNS SETOF RECORD AS
> >> $BODY$
> >>    DECLARE croid integer;
> >>    BEGIN
> >>
> >> 	--PERFORM A SMALL CALCULATION
> >> 	--DOESNT SEEM TO MATTER WHAT IT IS
> >>
> >> 	SELECT INTO croid 2;
> >>
> >> 	--A SELECT STATEMENT OUTPUTS RECORDS (one in this case)
> >> 	SELECT croid,$1;
> >>    END;
> >>
> >> $BODY$
> >>    LANGUAGE 'plpgsql' VOLATILE
> >>
> >>
> >>
> >>
> >> --The call looks like the following:
> >>
> >> SELECT test_function(1);
> >>
> >>
> >>
> >>
> >>
> >> --The resulting error reads as follows:
> >>
> >> ERROR:  query has no destination for result data
> >> HINT:  If you want to discard the results of a SELECT, use PERFORM
> instead.
> >> CONTEXT:  PL/pgSQL function "test_function" line 5 at SQL
> statement
> >>
> >> ********** Error **********
> >>
> >> ERROR: query has no destination for result data
> >> SQL state: 42601
> >> Hint: If you want to discard the results of a SELECT, use PERFORM
> instead.
> >> Context: PL/pgSQL function "test_function" line 5 at SQL statement
> > 
> > You have declared function to RETURN SETOF. In order for that to
> work you need 
> > to do RETURN NEXT. See below for difference between RETURN and
> RETURN NEXT:
> >
> http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS
> > 
> > 
> 
> 
> Thank you for the pointer.
> 
> I tried using FOR/RETURN NEXT as suggested but now get a
> different error:
> 
> 
> CREATE OR REPLACE FUNCTION test_function(integer)
>    RETURNS SETOF record AS
> $BODY$
>    DECLARE croid integer;
>    DECLARE R RECORD;
>    BEGIN
> 	SELECT INTO croid 2;
> 
> 	FOR R IN SELECT croid,$1 LOOP
> 	 RETURN NEXT R;
> 	END LOOP;
> 	RETURN;
>    END;
> 
> $BODY$
>    LANGUAGE 'plpgsql' VOLATILE
> 
> 
> There is now an error :
> 
> ERROR:  set-valued function called in context that cannot accept a
> set
> CONTEXT:  PL/pgSQL function "test_function" line 7 at RETURN NEXT
> 
> ********** Error **********
> 
> ERROR: set-valued function called in context that cannot accept a set
> SQL state: 0A000
> Context: PL/pgSQL function "test_function" line 7 at RETURN NEXT
> 
> 
> 
> PostgreSQL doesn't seem to see 'R' as being a
> SET OF RECORD....
> 
> 
> Peter

Did you happen to catch this:
Note that functions using RETURN NEXT or RETURN QUERY must be called as a table source in a FROM clause

Try:
select * from test_function(1)

Adrian Klaver
aklaver@comcast.net