Hello yes, this behave is strange, and should be fixed Regards Pavel Stehule 2011/5/21 Dan S <strd911@xxxxxxxxx>: > Hi ! > > 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 table tbl1 ( col1 int, constraint pk_tb1 primary key (col1)); > > insert into tbl1 values (1),(2),(3),(4),(5),(6); > > 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; > > select * from dynamic_query(4); > > > Best Regards > Dan S > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
