From surajitb@virtusa.com Mon Jul  13 03:33:58 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 6DC35635D77
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 13 Jul 2009 00:33:58 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 32095-07-4 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 13 Jul 2009 00:33:48 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp.virtusa.com (smtp.virtusa.com [12.40.51.197])
	by mail.postgresql.org (Postfix) with ESMTP id E0B396348B4
	for <pgsql-sql@postgresql.org>; Mon, 13 Jul 2009 00:33:43 -0300 (ADT)
X-TM-IMSS-Message-ID: <1a8dd51e00000cbb@smtp.virtusa.com>
Received: from ws-mailsvr.Virtusa.com (ws-mailsvr.virtusa.com [10.3.8.4]) by
	smtp.virtusa.com ([172.16.1.6]) with ESMTP (TREND IMSS SMTP
	Service 7.0) id 1a8dd51e00000cbb ; Sun, 12 Jul 2009 23:04:23 -0500
X-MimeOLE: Produced By Microsoft Exchange V6.5
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01CA0366.9F42E3B8"
Subject: function returning a cursor and a scalar 
Date: Sun, 12 Jul 2009 23:07:15 -0400
Message-ID: <75F63F40B35C3144854E90019EB086770DDC0229@ws-mailsvr.Virtusa.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: function returning a cursor and a scalar 
Thread-Index: AcoDZwY7Lxzj7oRDS8imndNpjXty8Q==
From: "Surajit Bhattacharjee" <surajitb@virtusa.com>
To: <pgsql-sql@postgresql.org>
X-TM-AS-Product-Ver: IMSS-7.0.0.6298-5.6.0.1016-16760.004
X-TM-AS-Result: No--7.820-5.0-31-1
X-imss-scan-details: No--7.820-5.0-31-1
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200907/61
X-Sequence-Number: 32802

This is a multi-part message in MIME format.


------_=_NextPart_001_01CA0366.9F42E3B8
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I=20am=20new=20to=20Postgresql=20and=20am=20trying=20to=20write=20a=20fun=
ction=20which=20will=20do=20a
search=20and=20return=20the=20first=20page=20of=20results=20along=20with=
=20the=20total=20number
of=20matches.=20How=20can=20I=20make=20my=20function=20return=20a=20curso=
r=20AND=20a=20scalar=20-
can=20I=20do=20the=20scalar=20as=20an=20OUT=20param=20and=20then=20make=
=20the=20function
explicitly=20RETURN=20a=20refcursor?

=20

Surajit


-------------------------------------------------------------------------=
--------------------

This=20message,=20including=20any=20attachments,=20contains=20confidentia=
l=20information=20intended=20for=20a=20specific=20individual=20and=20purp=
ose,=20and=20is=20intended=20for=20the=20addressee=20only.=20Any=20unauth=
orized=20disclosure,=20use,=20dissemination,=20copying,=20or=20distributi=
on=20of=20this=20message=20or=20any=20of=20its=20attachments=20or=20the=
=20information=20contained=20in=20this=20e-mail,=20or=20the=20taking=20of=
=20any=20action=20based=20on=20it,=20is=20strictly=20prohibited.=20If=20y=
ou=20are=20not=20the=20intended=20recipient,=20please=20notify=20the=20se=
nder=20immediately=20by=20return=20e-mail=20and=20delete=20this=20message=
.

-------------------------------------------------------------------------=
--------------------

------_=_NextPart_001_01CA0366.9F42E3B8
Content-Type: text/html;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable

<html=20xmlns:o=3D"urn:schemas-microsoft-com:office:office"=20xmlns:w=3D"=
urn:schemas-microsoft-com:office:word"=20xmlns=3D"http://www.w3.org/TR/RE=
C-html40">

<head>
<META=20HTTP-EQUIV=3D"Content-Type"=20CONTENT=3D"text/html;=20charset=3Du=
s-ascii">
<meta=20name=3DGenerator=20content=3D"Microsoft=20Word=2011=20(filtered=
=20medium)">
<style>
<!--
=20/*=20Font=20Definitions=20*/
=20@font-face
=09{font-family:"MS=20Mincho";
=09panose-1:2=202=206=209=204=202=205=208=203=204;}
@font-face
=09{font-family:Calibri;
=09panose-1:2=2015=205=202=202=202=204=203=202=204;}
@font-face
=09{font-family:"\@MS=20Mincho";
=09panose-1:0=200=200=200=200=200=200=200=200=200;}
=20/*=20Style=20Definitions=20*/
=20p.MsoNormal,=20li.MsoNormal,=20div.MsoNormal
=09{margin:0in;
=09margin-bottom:.0001pt;
=09font-size:12.0pt;
=09font-family:"Times=20New=20Roman";}
a:link,=20span.MsoHyperlink
=09{color:blue;
=09text-decoration:underline;}
a:visited,=20span.MsoHyperlinkFollowed
=09{color:purple;
=09text-decoration:underline;}
span.EmailStyle17
=09{mso-style-type:personal-compose;
=09font-family:Calibri;
=09color:black;
=09font-weight:normal;
=09font-style:normal;}
@page=20Section1
=09{size:8.5in=2011.0in;
=09margin:1.0in=201.25in=201.0in=201.25in;}
div.Section1
=09{page:Section1;}
-->
</style>

</head>

<body=20lang=3DEN-US=20link=3Dblue=20vlink=3Dpurple>

<div=20class=3DSection1>

<p=20class=3DMsoNormal><font=20size=3D3=20face=3D"Times=20New=20Roman"><s=
pan=20style=3D'font-size:
12.0pt'>I=20am=20new=20to=20Postgresql=20and=20am=20trying=20to=20write=
=20a=20function=20which=20will=20do
a=20search=20and=20return=20the=20first=20page=20of=20results=20along=20w=
ith=20the=20total=20number=20of
matches.=20How=20can=20I=20make=20my=20function=20return=20a=20cursor=20A=
ND=20a=20scalar=20&#8211;=20can=20I
do=20the=20scalar=20as=20an=20OUT=20param=20and=20then=20make=20the=20fun=
ction=20explicitly=20RETURN=20a
refcursor?<o:p></o:p></span></font></p>

<p=20class=3DMsoNormal><font=20size=3D3=20face=3D"Times=20New=20Roman"><s=
pan=20style=3D'font-size:
12.0pt'><o:p>&nbsp;</o:p></span></font></p>

<p=20class=3DMsoNormal><font=20size=3D3=20face=3D"Times=20New=20Roman"><s=
pan=20style=3D'font-size:
12.0pt'>Surajit<o:p></o:p></span></font></p>

</div>

</body>

</html>

<table><tr><td=20bgcolor=3D#ffffff><font=20color=3D#000000><pre>---------=
-------------------------------------------------------------------------=
-----------

This=20message,=20including=20any=20attachments,=20contains=20confidentia=
l=20information=20intended=20for=20a=20specific=20individual=20and=20purp=
ose,=20and=20is=20intended=20for=20the=20addressee=20only.=20Any=20unauth=
orized=20disclosure,=20use,=20dissemination,=20copying,=20or=20distributi=
on=20of=20this=20message=20or=20any=20of=20its=20attachments=20or=20the=
=20information=20contained=20in=20this=20e-mail,=20or=20the=20taking=20of=
=20any=20action=20based=20on=20it,=20is=20strictly=20prohibited.=20If=20y=
ou=20are=20not=20the=20intended=20recipient,=20please=20notify=20the=20se=
nder=20immediately=20by=20return=20e-mail=20and=20delete=20this=20message=
.

-------------------------------------------------------------------------=
--------------------</pre></font></td></tr></table>

------_=_NextPart_001_01CA0366.9F42E3B8--



From alvherre@commandprompt.com Mon Jul  13 18:27:41 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 4A21A634A71
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 13 Jul 2009 15:27:41 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 81437-02 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 13 Jul 2009 15:27:27 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from lists.commandprompt.com (host-159.commandprompt.net
	[207.173.203.159])
	by mail.postgresql.org (Postfix) with ESMTP id E977B636479
	for <pgsql-sql@postgresql.org>; Mon, 13 Jul 2009 15:21:35 -0300 (ADT)
Received: from perhan.alvh.no-ip.org (201-220-118-158.bk10-dsl.surnet.cl
	[201.220.118.158]) (authenticated bits=0)
	by lists.commandprompt.com (8.13.8/8.13.8) with ESMTP id n6DIU8jZ011839
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO);
	Mon, 13 Jul 2009 11:30:11 -0700
Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000)
	id C8254D419B; Mon, 13 Jul 2009 14:21:30 -0400 (CLT)
Date: Mon, 13 Jul 2009 14:21:30 -0400
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Surajit Bhattacharjee <surajitb@virtusa.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: function returning a cursor and a scalar
Message-ID: <20090713182130.GG4930@alvh.no-ip.org>
References: <75F63F40B35C3144854E90019EB086770DDC0229@ws-mailsvr.Virtusa.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <75F63F40B35C3144854E90019EB086770DDC0229@ws-mailsvr.Virtusa.com>
User-Agent: Mutt/1.5.18 (2008-05-17)
X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.0
	(lists.commandprompt.com [207.173.203.159]);
	Mon, 13 Jul 2009 11:30:11 -0700 (PDT)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL=0.006
X-Spam-Level: 
X-Archive-Number: 200907/67
X-Sequence-Number: 32808

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



From surajitb@virtusa.com Tue Jul  14 16:18:38 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 4D1746382C7
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 14 Jul 2009 13:18:38 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 75361-04-6 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 14 Jul 2009 13:18:26 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp.virtusa.com (smtp.virtusa.com [12.40.51.197])
	by mail.postgresql.org (Postfix) with ESMTP id E5A6563FD3D
	for <pgsql-sql@postgresql.org>; Tue, 14 Jul 2009 12:18:23 -0300 (ADT)
X-TM-IMSS-Message-ID: <225475f200001314@smtp.virtusa.com>
Received: from ws-mailsvr.Virtusa.com (ws-mailsvr.virtusa.com [10.3.8.4]) by
	smtp.virtusa.com ([172.16.1.6]) with ESMTP (TREND IMSS SMTP
	Service 7.0) id 225475f200001314 ; Tue, 14 Jul 2009 11:18:40 -0500
X-MimeOLE: Produced By Microsoft Exchange V6.5
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: function returning a cursor and a scalar
Date: Tue, 14 Jul 2009 11:21:37 -0400
Message-ID: <75F63F40B35C3144854E90019EB086770DDC05D7@ws-mailsvr.Virtusa.com>
In-Reply-To: <20090713182130.GG4930@alvh.no-ip.org>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] function returning a cursor and a scalar
Thread-Index: AcoD5uzBVM16aFtXSfe+AABOgR9QTQArwawQ
References: <75F63F40B35C3144854E90019EB086770DDC0229@ws-mailsvr.Virtusa.com>
	<20090713182130.GG4930@alvh.no-ip.org>
From: "Surajit Bhattacharjee" <surajitb@virtusa.com>
To: "Alvaro Herrera" <alvherre@commandprompt.com>
Cc: <pgsql-sql@postgresql.org>
X-TM-AS-Product-Ver: IMSS-7.0.0.6298-5.6.0.1016-16764.000
X-TM-AS-Result: No--6.431-5.0-31-1
X-imss-scan-details: No--6.431-5.0-31-1
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL=0.001
X-Spam-Level: 
X-Archive-Number: 200907/68
X-Sequence-Number: 32809

Thanks=20much=20Alvaro.=20Didn't=20know=20I=20could=20do=20that.

For=20returning=20the=20matches,=20I=20am=20planning=20to=20run=20a=20sep=
arate=20count(*)
query=20with=20the=20original=20WHERE=20clause=20minus=20joins=20that=20a=
re=20purely=20for
pulling=20addnl=20attributes=20from=20related=20entities.=20Only=20if=20t=
his=20query
returns=20a=20positive=20count,=20I=20will=20run=20the=20query=20that=20a=
ctually=20gets=20the
data.=20I=20google'd=20some=20and=20couldn't=20find=20a=20way=20to=20avoi=
d=20two=20queries.

Best=20Regards,
Surajit=20Bhattacharjee
__________________
Mobile:=20508-277-9091
VOIP:=209032


-----Original=20Message-----
From:=20Alvaro=20Herrera=20[mailto:alvherre@commandprompt.com]=20
Sent:=20Monday,=20July=2013,=202009=202:22=20PM
To:=20Surajit=20Bhattacharjee
Cc:=20pgsql-sql@postgresql.org
Subject:=20Re:=20[SQL]=20function=20returning=20a=20cursor=20and=20a=20sc=
alar

Surajit=20Bhattacharjee=20wrote:
>=20I=20am=20new=20to=20Postgresql=20and=20am=20trying=20to=20write=20a=
=20function=20which=20will=20do
a
>=20search=20and=20return=20the=20first=20page=20of=20results=20along=20w=
ith=20the=20total
number
>=20of=20matches.=20How=20can=20I=20make=20my=20function=20return=20a=20c=
ursor=20AND=20a=20scalar=20-
>=20can=20I=20do=20the=20scalar=20as=20an=20OUT=20param=20and=20then=20ma=
ke=20the=20function
>=20explicitly=20RETURN=20a=20refcursor?

Why=20wouldn't=20you=20just=20use=20two=20OUT=20params?

BTW=20how=20do=20you=20plan=20on=20returning=20the=20number=20of=20matche=
s?

--=20
Alvaro=20Herrera
http://www.CommandPrompt.com/
PostgreSQL=20Replication,=20Consulting,=20Custom=20Development,=2024x7=20=
support

-------------------------------------------------------------------------=
--------------------

This=20message,=20including=20any=20attachments,=20contains=20confidentia=
l=20information=20intended=20for=20a=20specific=20individual=20and=20purp=
ose,=20and=20is=20intended=20for=20the=20addressee=20only.=20Any=20unauth=
orized=20disclosure,=20use,=20dissemination,=20copying,=20or=20distributi=
on=20of=20this=20message=20or=20any=20of=20its=20attachments=20or=20the=
=20information=20contained=20in=20this=20e-mail,=20or=20the=20taking=20of=
=20any=20action=20based=20on=20it,=20is=20strictly=20prohibited.=20If=20y=
ou=20are=20not=20the=20intended=20recipient,=20please=20notify=20the=20se=
nder=20immediately=20by=20return=20e-mail=20and=20delete=20this=20message=
.

-------------------------------------------------------------------------=
--------------------