BUGs for function returning a SETOF values on linux postgres 8.0.4, the same function runs correctly on win postgres 8.1
TITLE:
BUGs for function returning a SETOF values on linux postgres 8.0.4, the same function with the same table runs correctly on win postgres 8.1
Dear Sir/Madam,
I am trying to run a very simple test on the above version. This test works fine on my window 8.1 version.
If I am taking out the function on the 8.0.4 version on linux as just the select (SELECT DISTINCT value FROM colors) I can see the results. If run via a function call I got the following error:
Failed to execute SQL : SQL select * from tt(); failed : ERROR: control reached end of function without RETURN CONTEXT: PL/pgSQL function "tt"
Please help to identify if this is a bug. Details attached.
Many thanks.
Susan
-- Type definition
CREATE TYPE otyperow AS
(f varchar);
-- Function definition
CREATE OR REPLACE FUNCTION tt()
RETURNS SETOF otyperow AS
$BODY$
DECLARE
oRow oTypeRow;
BEGIN
FOR oRow in SELECT DISTINCT value FROM colors LOOP
return next oRow;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
-- call the defined function
select * from tt();
-- Table field in colors table is a varchar(16).
Import Notes
Reference msg id not found: ED6D3B86ECB6B34EB5604F5068217CE80386CB5D@denexch01.den-eSniff.comReference msg id not found: ED6D3B86ECB6B34EB5604F5068217CE80386CB5F@denexch01.den-eSniff.com
"Susan Fu" <susan.fu@vericept.com> writes:
Failed to execute SQL : SQL select * from tt(); failed : ERROR: control =
reached end of function without RETURN CONTEXT: PL/pgSQL function "tt"
This is not a bug. 8.0 requires a RETURN in all cases. 8.1 is laxer
about it.
regards, tom lane
On Wed, Nov 30, 2005 at 02:53:44PM -0700, Susan Fu wrote:
I am trying to run a very simple test on the above version. This test
works fine on my window 8.1 version.
If I am taking out the function on the 8.0.4 version on linux as just
the select (SELECT DISTINCT value FROM colors) I can see the results.
If run via a function call I got the following error:Failed to execute SQL : SQL select * from tt(); failed : ERROR: control reached end of function without RETURN CONTEXT: PL/pgSQL function "tt"
The PL/pgSQL documentation for 8.0 and earlier says this:
The return value of a function cannot be left undefined. If control
reaches the end of the top-level block of the function without hitting
a RETURN statement, a run-time error will occur.
The 8.1 Release Notes show that this requirement has been removed:
* No longer require functions to issue a RETURN statement (Tom)
This is a byproduct of the newly added OUT and INOUT functionality.
RETURN can be omitted when it is not needed to provide the function's
return value.
--
Michael Fuhr