Re: counterintuitive behaviour in pl/pgsql

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
>