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
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 |