I'm running "PostgreSQL 9.0.2, compiled by Visual C++ build 1500, 32-bit"
I have found an odd behaviour in pl/pgsql when using 'return query execute' The function produce the dynamic query 'select * from tbl1 where col1 < 4' and executes it. I would have expected to have 3 rows back with the values 1,2,3 or maybe 3,3,3 but it returns all rows in the table ?? Here is a self contained test case that shows the behaviour. And yes I do know that I can fix the problem by renaming the output column to something else than i , I'm just curious about the behaviour and if it should work like this and why.
CREATE OR REPLACE FUNCTION dynamic_query(i int) RETURNS TABLE (i int) as $$ DECLARE stmt text; cond text; BEGIN stmt := 'select * from tbl1 ';
IF (i IS NOT NULL) THEN cond := ' col1 < $1 '; END IF; IF (cond IS NOT NULL) THEN stmt := stmt || 'where ' || cond; END IF; RETURN QUERY EXECUTE stmt USING i; RETURN; END; $$ language plpgsql;