Thread: counterintuitive behaviour in pl/pgsql

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

Hello

yes, this behave is strange, and should be fixed

Regards

Pavel Stehule

2011/5/21 Dan S <strd911@gmail.com>:
> 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
>

Hello,

seems like you cannot name your input parameters the same as your
tableoutputcolumns? Rename one of them and it works.

Something like:
RETURNS TABLE (j int)

Regards


Am 21.05.11 16:25, schrieb Pavel Stehule:
> Hello
>
> yes, this behave is strange, and should be fixed
>
> Regards
>
> Pavel Stehule
>
> 2011/5/21 Dan S<strd911@gmail.com>:
>> 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
>>
>

Dan S <strd911@gmail.com> writes:
> 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 $$

This should probably throw an error.  There is a check that disallows
having two input or two output parameters named the same, but the
comment about it says:

            /*
             * As of Postgres 9.0 we disallow using the same name for two
             * input or two output function parameters.  Depending on the
             * function's language, conflicting input and output names might
             * be bad too, but we leave it to the PL to complain if so.
             */

It looks like plpgsql didn't get the memo about checking this.

            regards, tom lane

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). 

Michael Glaesemann
grzm seespotcode net

Yes throwing an error would probably be good to catch these kind of mistakes which silently gives you the wrong answer otherwise.

Best Regards
Dan S

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
>

On May 21, 2011, at 13:44, Pavel Stehule wrote:

> 2011/5/21 Michael Glaesemann <grzm@seespotcode.net>:
>>
>> 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.

Yeah, I see that now (after seeing Tom's post). Thanks for the confirmation.

Michael Glaesemann
grzm seespotcode net