Обсуждение: 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