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