Re: counterintuitive behaviour in pl/pgsql - Mailing list pgsql-general
From | Pavel Stehule |
---|---|
Subject | Re: counterintuitive behaviour in pl/pgsql |
Date | |
Msg-id | BANLkTin+8Le-7tvYSPgYJ5M9-wYYSjdAkQ@mail.gmail.com Whole thread Raw |
In response to | Re: counterintuitive behaviour in pl/pgsql (Michael Glaesemann <grzm@seespotcode.net>) |
Responses |
Re: counterintuitive behaviour in pl/pgsql
|
List | pgsql-general |
2011/5/21 Michael Glaesemann <grzm@seespotcode.net>: > > On May 21, 2011, at 9:41, Dan S wrote: > >> 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); > > I couldn't see immediately what the issue was from the description as the example, so I came up with a couple of additionalexamples that helped me see what was going on: > > CREATE OR REPLACE FUNCTION dynamic_query_4(i int) > RETURNS TABLE (i int) > LANGUAGE PLPGSQL > AS $body$ > DECLARE > v_sql TEXT := 'SELECT col1 FROM tbl1 WHERE col1 < $1'; > BEGIN > RETURN QUERY EXECUTE v_sql USING i; > END; > $body$; > > SELECT * FROM dynamic_query_4(4); > i > --- > (0 rows) > > CREATE OR REPLACE FUNCTION dynamic_query_5(i int) > RETURNS TABLE (i int) > LANGUAGE PLPGSQL > AS $body$ > DECLARE > v_sql TEXT := 'SELECT col1 FROM tbl1'; > BEGIN > RAISE NOTICE 'i IS NULL => %', i IS NULL; > IF i IS NOT NULL THEN > v_sql := v_sql || ' WHERE col1 < $1'; > END IF; > RAISE NOTICE 'v_sql: %', v_sql; > RETURN QUERY EXECUTE v_sql USING i; > END; > $body$; > > SELECT * FROM dynamic_query_5(4); > > NOTICE: i IS NULL => t > NOTICE: v_sql: SELECT col1 FROM tbl1 > i > --- > 1 > 2 > 3 > 4 > 5 > 6 > (6 rows) > > It looks like it's just column names stomping on variable names, which is a known issue. This is why a lot of developers(including myself) have conventions of prefixing parameters and variable names (I use in_ for input parameters,v_ for internally defined variables). > It is not this case. There is two plpgsql variables with same name in one namespace - the last OUT variable has higher priority. Regards Pavel > Michael Glaesemann > grzm seespotcode net > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
pgsql-general by date: