writing a plpgsql query for the first time

From: "maxxedit(at)gmail(dot)com" <maxxedit(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: writing a plpgsql query for the first time
Date: 2011-03-16 10:43:37
Message-ID: AANLkTim3LtrZi0BdxhzfGcpvJs=EnkWpZH7MLWKpOsP0@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gabriele Bartolini 2011-03-16 11:02:05 Re: writing a plpgsql query for the first time
Previous Message Bill Thoen 2011-03-16 10:25:09 Re: Partitioned Database and Choosing Subtables