From: | Raymond O'Donnell <rod(at)iol(dot)ie> |
---|---|
To: | Nilesh Govindarajan <lists(at)itech7(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Procedures |
Date: | 2010-02-20 13:42:27 |
Message-ID: | 4B7FE6C3.4030702@iol.ie |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 20/02/2010 13:28, Nilesh Govindarajan wrote:
> Okay here's my query -
>
> select c.cid, c.subject, n.title from comments c, node n where c.nid =
> n.nid and c.status != 0;
>
> This is the query to check list of comments requiring admin approval and
> also the article titles on which this is posted.
>
> I want to see this result on the screen at psql prompt. Since it may
> return multiple rows, a cursor has to be employed here.
>
> Now if I employ a cursor here in the function/procedure, how to see the
> results ?
Have you declared your function to return SETOF the row type returned?
if so, you don't have to use a cursor, and the function will simply
return all the rows.
For example, using SQL (not tested):
create or replace function comments_for_approval()
returns setof record
as
$$
select c.cid, c.subject, n.title
from comments c, node n
where c.nid = n.nid
and c.status != 0;
$$
language sql;
....or something like that. If you use pl/pgsql, then you'll need to use
a different idiom:
create or replace function comments_for_approval()
returns setof record
as
$$
declare
m_rec record;
begin
for m_rec in
select c.cid, c.subject, n.title
from comments c, node n
where c.nid = n.nid
and c.status != 0
loop
return next m_rec;
end loop;
return;
end;
$$
language plpgsql;
Either way, simply execute the query in psql:
select * from comments_for_approval();
HTH,
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2010-02-20 13:43:55 | Re: Procedures |
Previous Message | Nilesh Govindarajan | 2010-02-20 13:28:28 | Re: Procedures |