Re: counterintuitive behaviour in pl/pgsql - Mailing list pgsql-general

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