Re: FUNCTION problem - Mailing list pgsql-sql

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;  BEGINSELECT 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