Re: writing a plpgsql query for the first time

Hi,

> test=# create function testfunc()
> test-# returns table (id int, code char(1)) as $$
> test$# BEGIN
> test$# return query select id, code from
> record_table where id > 2;
> test$# END;
> test$# $$ language plpgsql;

That's due to a clash in the identifiers' names. As you see you have an
'id' as output parameter, then 'id' in the query as part of the select
and an 'id' as part of the WHERE condition.

I suggest that you change the query and specify directly the name of
the fields, as follows:

return query select r.id, r.code from record_table r where r.id > 2;

Have a look on the documentation about this kind of issues, which has
been improved from version 9
(http://www.postgresql.org/docs/9.0/interactive/plpgsql-implementation.html#PLPGSQL-VAR-SUBST)

Cheers,
Gabriele

--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
Gabriele(dot)Bartolini(at)2ndQuadrant(dot)it - www.2ndQuadrant.it