Re: counterintuitive behaviour in pl/pgsql

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(at)gmail(dot)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
>>
>