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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
