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<strd...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
