From the_man.inmee@yahoo.com Tue Apr  3 07:44:38 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 8B033DE314A
	for <pgsql-sql@postgresql.org>; Tue,  3 Apr 2012 04:44:38 -0300 (ADT)
Received: from nm6-vm0.bullet.mail.bf1.yahoo.com ([98.139.213.146])
	by magus.postgresql.org with smtp (Exim 4.72)
	(envelope-from <the_man.inmee@yahoo.com>) id 1SEyPr-0000Od-Gt
	for pgsql-sql@postgresql.org; Tue, 03 Apr 2012 07:44:37 +0000
Received: from [98.139.215.142] by nm6.bullet.mail.bf1.yahoo.com with NNFMP;
	03 Apr 2012 07:44:21 -0000
Received: from [98.139.212.225] by tm13.bullet.mail.bf1.yahoo.com with NNFMP;
	03 Apr 2012 07:44:21 -0000
Received: from [127.0.0.1] by omp1034.mail.bf1.yahoo.com with NNFMP;
	03 Apr 2012 07:44:21 -0000
X-Yahoo-Newman-Property: ymail-3
X-Yahoo-Newman-Id: 202851.97822.bm@omp1034.mail.bf1.yahoo.com
Received: (qmail 57973 invoked by uid 60001); 3 Apr 2012 07:44:21 -0000
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024;
	t=1333439060; bh=snyMJdHwtBmUo2ilrf0btPmKmgV1harXHgAZ24h36Rg=;
	h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type;
	b=qwPLc8qW/S2ZNJCWmJjVvOrdZ0lS3MUWooXG4TPo9As4ZUP+MvGt613+9LNVzamRnONyeSXVOhBWXm2fivVM+ASbV5cb1vr3l+Xq4i2DC/cTjdXcSBA08Ryf4PE3xnk42/GpC409gV7i4MpZw3DP3L9dF94J67A0hHppw2UsXks=
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type;
	b=m6sYbN06A9sGQU5RD2y8LmC0kTBY/OFPfg4o93LR1zc5EoGzzfZm7lfqQkxfWS/AC93s8q9p/Evx413GICIHt3A2fcSiKryn2fMyL63/VZBhLsEGvGKUjj6wfRmQzgPIZkXJiJ9wVo786rpgET0lEN+8/oJ3jYyJiG7JXo8ZOFg=;
X-YMail-OSG: v41HJmoVM1nKfFYQeIRlkBZgwg4Yh7L4O6LErvi2KLiMAtP
	UxyibeH65rLfhH3rn.Ap2pZuvyeKnT8USUTV71F0bA8r6vbkm0Y9HHq1GWcK
	ZJ0CggmnLX4mKscz94hM9PO6RSKlnvzJflexMKUv0VhKGuoH_X.r6M_H3rj_
	zRzmxsitQUt.Q3HbAHZeNFzm0FavjsbbF5X3eBgOqOxI2QmH2_w3_rzW_mWw
	tW68TFVAE2AYhTmtrhBpeQj9w7uufwTI6qIxkckkMr6PlJPsq7G.x8byfv7o
	gnIpooX_6HKAzClsPd5i7aivqiZI5TeuJxpuxq7PqqXjMtYd7Udhn20psFrU
	SYi2XyduGrhbhVgSODCAZZufwicmakzCIeK_IoXxdu1Rmi7VwI5r2U_LzsJV
	1LcwYOH2YvzIfW6DUutnXSQwGpkZ9qg--
Received: from [182.178.164.154] by web162204.mail.bf1.yahoo.com via HTTP;
	Tue, 03 Apr 2012 00:44:20 PDT
X-Mailer: YahooMailWebService/0.8.117.340979
Message-ID: <1333439060.57914.YahooMailNeo@web162204.mail.bf1.yahoo.com>
Date: Tue, 3 Apr 2012 00:44:20 -0700 (PDT)
From: La Chi <the_man.inmee@yahoo.com>
Reply-To: La Chi <the_man.inmee@yahoo.com>
Subject: How to write cursors
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="15747-1817477543-1333439060=:57914"
X-Pg-Spam-Score: 1.6 (+)
X-Archive-Number: 201204/2
X-Sequence-Number: 36479

--15747-1817477543-1333439060=:57914
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hello everyone,=0A=0Ai have created this function in which i simply want to=
 display a column values through a cursor , but=A0unfortunately=A0i am not =
getting the column values , this function is showing me simple column name =
but not the =A0fields in column.=0A=0ACREATE OR REPLACE FUNCTION foo(id int=
) returns table(user_id int) as=0A$BODY$=0ADECLARE r int;=0ADECLARE rs curs=
or for Select userid from customer where area =3D'new_york';=0ABEGIN=0Aopen=
 rs ;=0Afetch next from rs into r;=0Awhile (found) loop=0Afetch Next from r=
s into r;=0AEND loop;=A0=0Aclose rs; =A0=0AEND;=0A$BODY$=0ALANGUAGE plpgsql=
;=0A=0A=0Ai simply want to show all the record from customer table where ar=
ea is New=A0York=A0but with the help of cursors.=0Athanks
--15747-1817477543-1333439060=:57914
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

<html><body><div style=3D"color:#000; background-color:#fff; font-family:ti=
mes new roman, new york, times, serif;font-size:12pt"><div style=3D"font-fa=
mily: 'times new roman', 'new york', times, serif; font-size: 12pt; ">Hello=
 everyone,</div><div style=3D"font-family: 'times new roman', 'new york', t=
imes, serif; font-size: 12pt; "><br></div><div><font face=3D"'times new rom=
an', 'new york', times, serif" size=3D"3">i have created this function in w=
hich i simply want to display a column values through a cursor , but&nbsp;<=
/font><font face=3D"'times new roman', 'new york', times, serif">unfortunat=
ely</font><font face=3D"'times new roman', 'new york', times, serif" size=
=3D"3">&nbsp;i am not getting the column values , this function is showing =
me simple column name but not the &nbsp;fields in column.</font></div><div =
style=3D"font-family: 'times new roman', 'new york', times, serif; font-siz=
e: 12pt; "><br></div><div><div><font face=3D"'times new roman', 'new york',=
 times,
 serif">CREATE OR REPLACE FUNCTION foo(id int) returns table(user_id int) a=
s</font></div><div><font face=3D"'times new roman', 'new york', times, seri=
f">$BODY$</font></div><div><font face=3D"'times new roman', 'new york', tim=
es, serif">DECLARE r int;</font></div><div><font face=3D"'times new roman',=
 'new york', times, serif">DECLARE rs cursor for Select userid from custome=
r where area =3D'new_york';</font></div><div><font face=3D"'times new roman=
', 'new york', times, serif">BEGIN</font></div><div><font face=3D"'times ne=
w roman', 'new york', times, serif"><span class=3D"Apple-tab-span" style=3D=
"white-space:pre">=09</span>open rs ;</font></div><div><font face=3D"'times=
 new roman', 'new york', times, serif"><span class=3D"Apple-tab-span" style=
=3D"white-space:pre">=09</span>fetch next from rs into r;</font></div><div>=
<font face=3D"'times new roman', 'new york', times, serif"><span class=3D"A=
pple-tab-span" style=3D"white-space:pre">=09</span>while (found) loop</font=
></div><div><font
 face=3D"'times new roman', 'new york', times, serif"><span class=3D"Apple-=
tab-span" style=3D"white-space:pre">=09</span>fetch Next from rs into r;</f=
ont></div><div><font face=3D"'times new roman', 'new york', times, serif"><=
span class=3D"Apple-tab-span" style=3D"white-space:pre">=09</span>END loop;=
&nbsp;</font></div><div><font face=3D"'times new roman', 'new york', times,=
 serif"><span class=3D"Apple-tab-span" style=3D"white-space:pre">=09</span>=
close rs; &nbsp;</font></div><div><font face=3D"'times new roman', 'new yor=
k', times, serif">END;</font></div><div><font face=3D"'times new roman', 'n=
ew york', times, serif">$BODY$</font></div><div><font face=3D"'times new ro=
man', 'new york', times, serif">LANGUAGE plpgsql;</font></div><div style=3D=
"font-family: 'times new roman', 'new york', times, serif; font-size: 12pt;=
 "><br></div><div style=3D"font-family: 'times new roman', 'new york', time=
s, serif; font-size: 12pt; "><br></div><div><font face=3D"'times new roman'=
, 'new york', times, serif"
 size=3D"3">i simply want to show all the record from customer table where =
area is New&nbsp;</font><font face=3D"'times new roman', 'new york', times,=
 serif">York</font><font face=3D"'times new roman', 'new york', times, seri=
f" size=3D"3">&nbsp;but with the help of cursors.</font></div><div><font fa=
ce=3D"'times new roman', 'new york', times, serif" size=3D"3">thanks</font>=
</div></div></div></body></html>
--15747-1817477543-1333439060=:57914--



From the_man.inmee@yahoo.com Wed Apr  4 07:30:46 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id CED334BB8E6
	for <pgsql-sql@postgresql.org>; Wed,  4 Apr 2012 04:30:46 -0300 (ADT)
Received: from nm17-vm0.bullet.mail.bf1.yahoo.com ([98.139.213.157])
	by magus.postgresql.org with smtp (Exim 4.72)
	(envelope-from <the_man.inmee@yahoo.com>) id 1SFKfy-0003Dm-Lv
	for pgsql-sql@postgresql.org; Wed, 04 Apr 2012 07:30:45 +0000
Received: from [98.139.212.153] by nm17.bullet.mail.bf1.yahoo.com with NNFMP;
	04 Apr 2012 07:30:29 -0000
Received: from [98.139.212.237] by tm10.bullet.mail.bf1.yahoo.com with NNFMP;
	04 Apr 2012 07:30:29 -0000
Received: from [127.0.0.1] by omp1046.mail.bf1.yahoo.com with NNFMP;
	04 Apr 2012 07:30:29 -0000
X-Yahoo-Newman-Property: ymail-3
X-Yahoo-Newman-Id: 77367.45688.bm@omp1046.mail.bf1.yahoo.com
Received: (qmail 43239 invoked by uid 60001); 4 Apr 2012 07:30:29 -0000
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024;
	t=1333524629; bh=ZX0iSdjeTGYJfBiooACBG6irqtE3VDLTNc2rf66sAPg=;
	h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type;
	b=uj9MHqEDFWkb6Nm95hukkrDP9ZGmfwbbd7LskMNT8XttNe5eSfKV1tFMgvAb+hsy5OdjDeZWkDTdGvdRMCCdASGIYDD2CzGM3BF2sCqQWaZwYLaE4tCIpKLSKfclOaP49ZZbG2LYmNaZ13LePRLy8iGBXy8Srmi3ZPfsfRrU8VE=
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type;
	b=Z/gkK+P2KCrK6NlWssrRCDTE2XHcocbrTxgeBryaf7lPPSafjpqc8KvxCxSPl/2D4x7c090i74Lg8zSzUq4hIPYbAXaSxfMVGxH9Y9woqeqkO+jOk4R0V0x4+LZNAyF3Klfc8k5zRTrtWX/DzfnFvZCfeKZqaAOtqvciq3Iok1c=;
X-YMail-OSG: kSmCJdYVM1l3iGOEvQM5J_QrVal1hU6O7D3A7MmWIXfE2Tx
	KG6RszMfGe8BaVmwbT3QzpR.vzSIdRqAvbxW9mNfZ3lVpYPq5jVxBWagusjQ
	6ysiKPEtgvowTTb8_j00BB0wa6NlZCYGhWJ0lLKQoOh_mcRy5m_Dwb.yC8nq
	X7nK56cuYBkWqsw6KAeshnkDqJqAPiQ4phsSIFhFufQ7c5QKAjLqgrqVNePp
	EtXu4WBx_KM1iIRZtBOhAr5OIAR8AbV4Mq6ERG.b7mmp9iMe.mVb6FstZkPT
	Z_WcK0UnnFVRnJgNn1qzgBkk8URqevwQ0RGIw8sFJfSWdFepIgcZqAPjCf_y
	1zJDY_SyUOb5ZX5q6nbugiz1wtKRx5WA.7AmWSwmFUP_GKlZIDeE8vsJkI1U
	5AMgK5LZumyPZazOisagekSvd0ytM6Q--
Received: from [182.177.203.249] by web162203.mail.bf1.yahoo.com via HTTP;
	Wed, 04 Apr 2012 00:30:28 PDT
X-Mailer: YahooMailWebService/0.8.117.340979
Message-ID: <1333524628.40393.YahooMailNeo@web162203.mail.bf1.yahoo.com>
Date: Wed, 4 Apr 2012 00:30:28 -0700 (PDT)
From: La Chi <the_man.inmee@yahoo.com>
Reply-To: La Chi <the_man.inmee@yahoo.com>
Subject: how to write cursors
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="900607865-617215373-1333524628=:40393"
X-Pg-Spam-Score: 1.6 (+)
X-Archive-Number: 201204/7
X-Sequence-Number: 36484

--900607865-617215373-1333524628=:40393
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

hi every one=0A=0Ai have created this simple function which returns a colum=
n of table , i have used simple SELECT statement , i simply want to know ho=
w can i=A0achieve=A0the same task with the help of cursor=0A=0ACREATE OR RE=
PLACE FUNCTION foo(id int)=0Areturns table(cust_id int) as=0A$BODY$=0A=0ABE=
GIN=0A=A0 =A0 =A0 =A0=0Areturn query Select userid from customer where pron=
ame =3D'shampoo';=0AEND;=0A$BODY$=0ALANGUAGE plpgsql;=0A=0Ahow can i write =
a cursor which should return all the custid who had proname shampoo . i sha=
ll be very thankful to you=A0=0A
--900607865-617215373-1333524628=:40393
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

<html><body><div style=3D"color:#000; background-color:#fff; font-family:ti=
mes new roman, new york, times, serif;font-size:12pt"><div style=3D"font-fa=
mily: 'times new roman', 'new york', times, serif; font-size: 12pt; ">hi ev=
ery one</div><div style=3D"font-family: 'times new roman', 'new york', time=
s, serif; font-size: 12pt; "><br></div><div><font face=3D"'times new roman'=
, 'new york', times, serif" size=3D"3">i have created this simple function =
which returns a column of table , i have used simple SELECT statement , i s=
imply want to know how can i&nbsp;</font><font face=3D"'times new roman', '=
new york', times, serif">achieve</font><font face=3D"'times new roman', 'ne=
w york', times, serif" size=3D"3">&nbsp;the same task with the help of curs=
or</font></div><div><font face=3D"'times new roman', 'new york', times, ser=
if" size=3D"3"><br></font></div><div><font face=3D"'times new roman', 'new =
york', times, serif" size=3D"3"><div>CREATE OR REPLACE FUNCTION foo(id
 int)</div><div>returns table(cust_id int) as</div><div>$BODY$</div><div><b=
r></div><div>BEGIN<span class=3D"Apple-tab-span" style=3D"white-space:pre">=
=09</span></div><div>&nbsp; &nbsp; &nbsp; &nbsp;</div><div><span class=3D"A=
pple-tab-span" style=3D"white-space:pre">=09=09</span>return query Select u=
serid from customer where proname =3D'shampoo';</div><div><span class=3D"Ap=
ple-tab-span" style=3D"white-space:pre">=09=09</span></div><div>END;</div><=
div>$BODY$</div><div>LANGUAGE plpgsql;</div><div><br></div><div>how can i w=
rite a cursor which should return all the custid who had proname shampoo . =
i shall be very thankful to you&nbsp;</div><div><br></div></font></div></di=
v></body></html>
--900607865-617215373-1333524628=:40393--



From pavel.stehule@gmail.com Wed Apr  4 07:37:13 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id C5E564BB8E6
	for <pgsql-sql@postgresql.org>; Wed,  4 Apr 2012 04:37:13 -0300 (ADT)
Received: from mail-qa0-f42.google.com ([209.85.216.42])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <pavel.stehule@gmail.com>) id 1SFKmG-0007F5-8k
	for pgsql-sql@postgresql.org; Wed, 04 Apr 2012 07:37:13 +0000
Received: by qafi31 with SMTP id i31so287005qaf.1
	for <pgsql-sql@postgresql.org>; Wed, 04 Apr 2012 00:36:59 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type:content-transfer-encoding;
	bh=xtjafIKJynRstRk9F6HAlleiV1b3izL4/zBSMhOHT/U=;
	b=HAo7+7iDk2ridihKBkASiv283WCMkdNfctH1vHUzp1qUnvr/RanHhoVIKupt0ZI7zs
	V6StCowrTofpqME8SCHDb4NiEmsqDqARYqPAtDofGXKK3XTtUq1GT6STm45G5y+7MwC+
	5E6yavTlbJvI1dNeBUVFmqTxjbEQlA2/6fnKI6HNVb1T3wSg4EQ/ZAUYGIgPGwW+wMDv
	GkCiD697zlkHQXCq7KP28XPlzEwnGQADQDn75ErgrZswPtSqwRNvvCYOlEDmqsA0H1lX
	ZoW+7wuPNANKvz3heZy6PYvgCrhzotskLWyK9pEsP1f0gTsg2X+EYSr1c+/qgzxnVyDI
	hRmA==
Received: by 10.224.109.65 with SMTP id i1mr3118035qap.39.1333525019542; Wed,
	04 Apr 2012 00:36:59 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.229.238.10 with HTTP; Wed, 4 Apr 2012 00:36:19 -0700 (PDT)
In-Reply-To: <1333524628.40393.YahooMailNeo@web162203.mail.bf1.yahoo.com>
References: <1333524628.40393.YahooMailNeo@web162203.mail.bf1.yahoo.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Wed, 4 Apr 2012 09:36:19 +0200
Message-ID: 
 <CAFj8pRCEe9CxX6P0Zg+JHCti6u7in2VO-BSogTJt5T1ucZxq0A@mail.gmail.com>
Subject: Re: how to write cursors
To: La Chi <the_man.inmee@yahoo.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201204/8
X-Sequence-Number: 36485

Hello

use a refcursors

http://www.postgresql.org/docs/9.1/static/plpgsql-cursors.html

Regards

Pavel Stehule

2012/4/4 La Chi <the_man.inmee@yahoo.com>:
> hi every one
>
> i have created this simple function which returns a column of table , i h=
ave
> used simple SELECT statement , i simply want to know how can i=C2=A0achie=
ve=C2=A0the
> same task with the help of cursor
>
> CREATE OR REPLACE FUNCTION foo(id int)
> returns table(cust_id int) as
> $BODY$
>
> BEGIN
>
> return query Select userid from customer where proname =3D'shampoo';
> END;
> $BODY$
> LANGUAGE plpgsql;
>
> how can i write a cursor which should return all the custid who had prona=
me
> shampoo . i shall be very thankful to you
>