Thread: writing a plpgsql query for the first time

Hi,

I am having trouble writing my first plpgsql query. Any idea why the
following plpgsql does not work??

test=# drop function if exists testfunc() ;
DROP FUNCTION
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;
CREATE FUNCTION
test=#
test=# select * from testfunc();
 id | code
----+------
(0 rows)


test=# select * from record_table;
 id | code
----+------
  1 | 1
  3 | 3
(2 rows)


If I take out "id > 2" from the function, in the where clause, it returns:
test=# select * from testfunc();
NOTICE:  hi
 id | code
----+------
    |
    |
(2 rows)

2 rows...but no actual data??

Here is the table definition:
test=# \d record_table;
                         Table "public.record_table"
 Column |     Type     |                      Modifiers
--------+--------------+-----------------------------------------------------
 id     | integer      | not null default nextval('record_id_seq'::regclass)
 code   | character(1) |
Indexes:
    "record_pkey" PRIMARY KEY, btree (id)


Any idea what's happening?

thanks

 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