Thread: Problem with function returning a result set
Hi, I'm playing around with functions returning result sets, and I have a problem with the following function: -- Create sample data CREATE TABLE employee (id integer, first_name varchar(50), last_name varchar(50)); INSERT INTO employee values (1, 'Arthur', 'Dent'); INSERT INTO employee values (2, 'Zaphod', 'Beeblebrox'); INSERT INTO employee values (3, 'Ford', 'Prefect'); COMMIT; -- Create the function CREATE OR REPLACE FUNCTION get_employees(name_pattern varchar) RETURNS TABLE(id integer, full_name text) AS $$ BEGIN RETURN QUERY SELECT id, first_name||' '||last_name FROM employee WHERE last_name LIKE name_pattern ||'%'; END $$ LANGUAGE plpgsql; COMMIT; Now when I run: SELECT * FROM get_employees('D'); I get one row returned which is correct, but the ID column is null (but should be 1). It does not depend which row(s) I selectthrough the procedure. I also tried to change the datatype of the returned id to int8 and an explicit cast in the SELECTstatement, but to no avail. When I define the function using SQL as a language (with the approriate changes), the ID column is returned correctly. I'm using Postgres 8.4.3 on Windows XP postgres=> select version(); version ------------------------------------------------------------- PostgreSQL 8.4.3, compiled by Visual C++ build 1400, 32-bit (1 row) What am I missing? Regards Thomas
Thomas Kellerer <spam_eater@gmx.net> writes: > CREATE OR REPLACE FUNCTION get_employees(name_pattern varchar) > RETURNS TABLE(id integer, full_name text) > AS > $$ > BEGIN > RETURN QUERY > SELECT id, first_name||' '||last_name > FROM employee > WHERE last_name LIKE name_pattern ||'%'; > END > $$ > LANGUAGE plpgsql; > I get one row returned which is correct, but the ID column is null > (but should be 1). Don't name the parameter the same as the table column ... regards, tom lane
2010/4/8 Thomas Kellerer <spam_eater@gmx.net>: > Hi, > > I'm playing around with functions returning result sets, and I have a > problem with the following function: > > -- Create sample data > CREATE TABLE employee (id integer, first_name varchar(50), last_name > varchar(50)); > INSERT INTO employee values (1, 'Arthur', 'Dent'); > INSERT INTO employee values (2, 'Zaphod', 'Beeblebrox'); > INSERT INTO employee values (3, 'Ford', 'Prefect'); > COMMIT; > > -- Create the function > CREATE OR REPLACE FUNCTION get_employees(name_pattern varchar) > RETURNS TABLE(id integer, full_name text) > AS > $$ > BEGIN > > RETURN QUERY > SELECT id, first_name||' '||last_name > FROM employee > WHERE last_name LIKE name_pattern ||'%'; > END > $$ > LANGUAGE plpgsql; > > COMMIT; > > Now when I run: > > SELECT * > FROM get_employees('D'); > > I get one row returned which is correct, but the ID column is null (but > should be 1). It does not depend which row(s) I select through the > procedure. I also tried to change the datatype of the returned id to int8 > and an explicit cast in the SELECT statement, but to no avail. > > When I define the function using SQL as a language (with the approriate > changes), the ID column is returned correctly. > > I'm using Postgres 8.4.3 on Windows XP > postgres=> select version(); > version > ------------------------------------------------------------- > PostgreSQL 8.4.3, compiled by Visual C++ build 1400, 32-bit > (1 row) > > What am I missing? there are collision between SQL and PLpgSQL identifiers. RETURNS TABLE(id integer, full_name text) AS$$BEGIN RETURN QUERY SELECT e.id, e.first_name||' '||e.last_name FROM employeee WHERE e.last_name LIKE e.name_pattern ||'%';END$$LANGUAGE plpgsql; use aliases. Regards Pavel Stehule > > Regards > Thomas > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Tom Lane, 08.04.2010 10:59: > Thomas Kellerer<spam_eater@gmx.net> writes: >> CREATE OR REPLACE FUNCTION get_employees(name_pattern varchar) >> RETURNS TABLE(id integer, full_name text) >> AS >> $$ >> BEGIN > >> RETURN QUERY >> SELECT id, first_name||' '||last_name >> FROM employee >> WHERE last_name LIKE name_pattern ||'%'; >> END >> $$ >> LANGUAGE plpgsql; > >> I get one row returned which is correct, but the ID column is null >> (but should be 1). > > Don't name the parameter the same as the table column ... > > regards, tom lane > I knew it was something simple I overlooked ;) Thanks for the quick response. Regards Thomas