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