counterintuitive behaviour in pl/pgsql

Hello

yes, this behave is strange, and should be fixed

Regards

Pavel Stehule

2011/5/21 Dan S <strd911@gmail.com>:

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:

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 additional examples 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 Tom Lane <tgl@sss.pgh.pa.us>

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 additional examples 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

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