Thread: function returning a cursor and a scalar
<div class="Section1"><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12.0pt">I am new to Postgresql and am trying to write a function which will do a search and return the first page of resultsalong with the total number of matches. How can I make my function return a cursor AND a scalar – can I do the scalaras an OUT param and then make the function explicitly RETURN a refcursor?</span></font><p class="MsoNormal"><font face="TimesNew Roman" size="3"><span style="font-size: 12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12.0pt">Surajit</span></font></div><table><tr><td bgcolor="#ffffff"><font color="#000000"><pre>--------------------------------------------------------------------------------------------- This message, including any attachments, contains confidential information intended for a specific individual and purpose,and is intended for the addressee only. Any unauthorized disclosure, use, dissemination, copying, or distributionof this message or any of its attachments or the information contained in this e-mail, or the taking of any actionbased on it, is strictly prohibited. If you are not the intended recipient, please notify the sender immediately byreturn e-mail and delete this message. ---------------------------------------------------------------------------------------------</pre></font></td></tr></table>
Surajit Bhattacharjee wrote: > I am new to Postgresql and am trying to write a function which will do a > search and return the first page of results along with the total number > of matches. How can I make my function return a cursor AND a scalar - > can I do the scalar as an OUT param and then make the function > explicitly RETURN a refcursor? Why wouldn't you just use two OUT params? BTW how do you plan on returning the number of matches? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Thanks much Alvaro. Didn't know I could do that. For returning the matches, I am planning to run a separate count(*) query with the original WHERE clause minus joins that are purely for pulling addnl attributes from related entities. Only if this query returns a positive count, I will run the query that actually gets the data. I google'd some and couldn't find a way to avoid two queries. Best Regards, Surajit Bhattacharjee __________________ Mobile: 508-277-9091 VOIP: 9032 -----Original Message----- From: Alvaro Herrera [mailto:alvherre@commandprompt.com] Sent: Monday, July 13, 2009 2:22 PM To: Surajit Bhattacharjee Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] function returning a cursor and a scalar Surajit Bhattacharjee wrote: > I am new to Postgresql and am trying to write a function which will do a > search and return the first page of results along with the total number > of matches. How can I make my function return a cursor AND a scalar - > can I do the scalar as an OUT param and then make the function > explicitly RETURN a refcursor? Why wouldn't you just use two OUT params? BTW how do you plan on returning the number of matches? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support --------------------------------------------------------------------------------------------- This message, including any attachments, contains confidential information intended for a specific individual and purpose,and is intended for the addressee only. Any unauthorized disclosure, use, dissemination, copying, or distributionof this message or any of its attachments or the information contained in this e-mail, or the taking of any actionbased on it, is strictly prohibited. If you are not the intended recipient, please notify the sender immediately byreturn e-mail and delete this message. ---------------------------------------------------------------------------------------------