Search Postgresql Archives

counterintuitive behaviour in pl/pgsql

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux