Обсуждение: BUG #6031: Bug with plpgsql function and RETURNS TABLE
The following bug has been logged online: Bug reference: 6031 Logged by: Preston M. Price Email address: preston@clearwateranalytics.com PostgreSQL version: 8.4 Operating system: Linux (Ubuntu) Description: Bug with plpgsql function and RETURNS TABLE Details: If I create a plpgsql function and use RETURNS TABLE the returned result set is filled with null values rather than the values from the table. ---SETUP--- create table test ( ID SERIAL PRIMARY KEY, VAL INT NOT NULL ); INSERT INTO TEST(VAL) VALUES(1); INSERT INTO TEST(VAL) VALUES(2); INSERT INTO TEST(VAL) VALUES(3); INSERT INTO TEST(VAL) VALUES(4); INSERT INTO TEST(VAL) VALUES(5); CREATE FUNCTION TEST_FUNC() RETURNS TABLE(val int) AS $$ BEGIN RETURN QUERY SELECT VAL FROM test; END; $$ LANGUAGE plpgsql; ---SETUP--- select * from TEST_FUNC(); This query yields 5 rows of null rather than the values from the test table.
Excerpts from Preston M. Price's message of vie may 20 12:12:09 -0400 2011: > If I create a plpgsql function and use RETURNS TABLE > the returned result set is filled with null values rather than the values > from the table. > select * from TEST_FUNC(); > > This query yields 5 rows of null rather than the values from the test table. Yeah, known problem. Don't use the column name as variable name; or you can qualify the column name in the query, like this: alvherre=# CREATE or replace FUNCTION TEST_FUNC() RETURNS TABLE(val int) AS $$ BEGIN RETURN QUERY SELECT test.val FROM test; END; $$ LANGUAGE plpgsql; CREATE FUNCTION alvherre=# select * from TEST_FUNC(); val ----- 1 2 3 4 5 (5 filas) -- Ãlvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support