From: Peter Willis <peterw@borstad.com>
To: Adrian Klaver <aklaver@comcast.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: FUNCTION problem
Date: Thu, 02 Apr 2009 09:09:56 -0700
Message-ID: <49D4E354.9090106@borstad.com> (raw)
In-Reply-To: <200904011655.58801.aklaver@comcast.net>
References: <49D3F948.4060908@borstad.com>
<200904011655.58801.aklaver@comcast.net>
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
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: peterw@borstad.com, aklaver@comcast.net
Subject: Re: FUNCTION problem
In-Reply-To: <49D4E354.9090106@borstad.com>
* 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