Thread: Procedures

How do I create a procedure using plpgsql cursors to print the output of
the query in the cursor (using for loop) ?

In all docs I found, it seems to be a must to return data to the call
which is not what I want.

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

Nilesh Govindarajan wrote:
> How do I create a procedure using plpgsql cursors to print the output
> of the query in the cursor (using for loop) ?
>
> In all docs I found, it seems to be a must to return data to the call
> which is not what I want.
>

what is it going to print it on?   the postgres server processes have no
console or stdout device.

On 02/20/2010 02:32 PM, John R Pierce wrote:
> Nilesh Govindarajan wrote:
>> How do I create a procedure using plpgsql cursors to print the output
>> of the query in the cursor (using for loop) ?
>>
>> In all docs I found, it seems to be a must to return data to the call
>> which is not what I want.
>>
>
> what is it going to print it on? the postgres server processes have no
> console or stdout device.

Okay, so how do I print it to stdout ?

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

Nilesh Govindarajan wrote on 20.02.2010 14:08:
> On 02/20/2010 02:32 PM, John R Pierce wrote:
>> Nilesh Govindarajan wrote:
>>> How do I create a procedure using plpgsql cursors to print the output
>>> of the query in the cursor (using for loop) ?
>>>
>>> In all docs I found, it seems to be a must to return data to the call
>>> which is not what I want.
>>>
>>
>> what is it going to print it on? the postgres server processes have no
>> console or stdout device.
>
> Okay, so how do I print it to stdout ?
>
Even if you could, that would be stdout of the *server*, not the one of the client calling the procedure!

Regards
Thomas

On 02/20/2010 06:51 PM, Thomas Kellerer wrote:
> Nilesh Govindarajan wrote on 20.02.2010 14:08:
>> On 02/20/2010 02:32 PM, John R Pierce wrote:
>>> Nilesh Govindarajan wrote:
>>>> How do I create a procedure using plpgsql cursors to print the output
>>>> of the query in the cursor (using for loop) ?
>>>>
>>>> In all docs I found, it seems to be a must to return data to the call
>>>> which is not what I want.
>>>>
>>>
>>> what is it going to print it on? the postgres server processes have no
>>> console or stdout device.
>>
>> Okay, so how do I print it to stdout ?
>>
> Even if you could, that would be stdout of the *server*, not the one of
> the client calling the procedure!
>
> Regards
> Thomas
>
>
>

Okay how to stdout it to the client ?

The reason is, I have a query which I need to run repeatedly to see if
some data has been inserted by the web application. So its irritating to
type the query again and again.

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

On 20/02/2010 13:08, Nilesh Govindarajan wrote:
> On 02/20/2010 02:32 PM, John R Pierce wrote:
>> Nilesh Govindarajan wrote:
>>> How do I create a procedure using plpgsql cursors to print the output
>>> of the query in the cursor (using for loop) ?
>>>
>>> In all docs I found, it seems to be a must to return data to the call
>>> which is not what I want.
>>>
>>
>> what is it going to print it on? the postgres server processes have no
>> console or stdout device.
>
> Okay, so how do I print it to stdout ?

Well, it still has to get back to the client from the server - hence you
need to return the data.... stout here will be stdout of the server, not
the client.

Maybe if you say more clearly what it is you *do* want, rather than what
you don't, people may be able to help you. :-)

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 02/20/2010 06:54 PM, Raymond O'Donnell wrote:
> On 20/02/2010 13:08, Nilesh Govindarajan wrote:
>> On 02/20/2010 02:32 PM, John R Pierce wrote:
>>> Nilesh Govindarajan wrote:
>>>> How do I create a procedure using plpgsql cursors to print the output
>>>> of the query in the cursor (using for loop) ?
>>>>
>>>> In all docs I found, it seems to be a must to return data to the call
>>>> which is not what I want.
>>>>
>>>
>>> what is it going to print it on? the postgres server processes have no
>>> console or stdout device.
>>
>> Okay, so how do I print it to stdout ?
>
> Well, it still has to get back to the client from the server - hence you
> need to return the data.... stout here will be stdout of the server, not
> the client.
>
> Maybe if you say more clearly what it is you *do* want, rather than what
> you don't, people may be able to help you. :-)
>
> Ray.
>

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 ?

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

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@iol.ie

Nilesh Govindarajan wrote on 20.02.2010 14:28:
> 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.

Hmm, I don't understand your question.

When you run the query, psql will display the result...

Thomas

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

On 20/02/2010 13:54, Nilesh Govindarajan wrote:

>
> Ah perfect ! problem solved. Thanks !
>

Glad it was that easy! You ought to read up on set-returning functions
in the docs:


http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

See the section on "RETURN NEXT and RETURN QUERY".

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie