Re: Procedures

On 02/20/2010 07:12 PM, Raymond O'Donnell wrote:
> 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.
>
>

Ah perfect ! problem solved. Thanks !

--
Nilesh Govindarajan
Site & Server Adminstrator
www.itech7.com