From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Dan S <strd911(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: counterintuitive behaviour in pl/pgsql |
Date: | 2011-05-21 14:25:04 |
Message-ID: | BANLkTinXkqqvcFhNSAssnpJwjBbpNcsbRg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ben Chobot | 2011-05-21 14:55:54 | understanding pg_locks |
Previous Message | Seb | 2011-05-21 13:47:41 | Re: length of return value of to_char() |