Re: writing a plpgsql query for the first time - Mailing list pgsql-general

 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.Bartolini@2ndQuadrant.it - www.2ndQuadrant.it