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