From: Adrian Klaver <aklaver@comcast.net>
To: peterw@borstad.com
Cc: pgsql-sql@postgresql.org
Subject: Re: FUNCTION problem
Date: Thu, 2 Apr 2009 22:20:11 +0000 (UTC)
Message-ID: <1521645855.2691471238710811603.JavaMail.root@sz0030a.emeryville.ca.mail.comcast.net> (raw)
In-Reply-To: <49D4E354.9090106@borstad.com>
----- "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-STRUC...
> >
> >
>
>
> 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
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: aklaver@comcast.net, peterw@borstad.com
Subject: Re: FUNCTION problem
In-Reply-To: <1521645855.2691471238710811603.JavaMail.root@sz0030a.emeryville.ca.mail.comcast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox