Hi,

Take a look at this piece of code:

create table tmp (a integer);

create or replace function fun() returns void as $$
declare
c cursor for select 10 as x union select 20 as x;
i integer := 20;
begin
perform mod(a, 10), min(a) from tmp group by mod(a, 10); -- query
with value
perform mod(a, i), min(a), a from tmp group by mod(a, i); --
query with variable
for rec in c loop
perform mod(a, rec.x), min(a) from tmp group by mod(a,
rec.x); -- query with cursor field
end loop;
end;
$$ language 'plpgsql';

select fun();

This results in:
ERROR: column "tmp.a" must appear in the GROUP BY clause or be used
in an aggregate function
LINE 1: SELECT mod(a, $1 ), min(a) from tmp group by mod(a, $2 )
^
QUERY: SELECT mod(a, $1 ), min(a) from tmp group by mod(a, $2 )
CONTEXT: PL/pgSQL function "fun" line 8 at PERFORM

With value or variable, this statement is parsed as: "SELECT mod(a,
$1 ), min(a) from tmp group by mod(a, $1 )", with cursor field, each
occurence of "rec.x" is replaced by its own var: $1, $2.

Possible workaround is to rewrite the loop to use a variable in the
query:
for rec in c loop
i := rec.x;
perform mod(a, i), min(a) from tmp group by mod(a, i);
end loop;

But anyway... is this behaviour intentional? A bug? Or am I doing
something wrong?

Thanks.

On 2010-05-01, kmehkeri <kmehkeri (AT) gmail (DOT) com> wrote:
you missed:
rec record;

Feels like a bug to me.
Send it here for an expert opinion: pgsql-bugs AT postgresql.org
(it's a mailing list)







--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---