From ramkumar.yelai@siemens.com Fri Aug  31 14:56:23 2012
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ramkumar.yelai@siemens.com>) id 1T7Sdz-0005yJ-D0
	for pgsql-sql@postgresql.org; Fri, 31 Aug 2012 14:56:23 +0000
Received: from krishna.sisl.co.in ([194.138.248.75])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ramkumar.yelai@siemens.com>) id 1T7Sdv-0006CD-Lo
	for pgsql-sql@postgresql.org; Fri, 31 Aug 2012 14:56:22 +0000
Received: from kaveri.sisl.co.in (kaveri.sisl.co.in [132.186.192.4])
	by krishna.sisl.co.in (Postfix) with ESMTP id D451413DD3C
	for <pgsql-sql@postgresql.org>; Fri, 31 Aug 2012 20:59:18 +0530 (IST)
Received: from INBLRK77N2MSX.in002.siemens.net
	(inblrk77n2msx.in002.siemens.net [132.186.221.163])
	by kaveri.sisl.co.in (Postfix) with ESMTP id 7D2C43E6307
	for <pgsql-sql@postgresql.org>; Fri, 31 Aug 2012 20:06:05 +0530 (IST)
Received: from INBLRK77M2MSX.in002.siemens.net ([169.254.2.42]) by
	INBLRK77N2MSX.in002.siemens.net ([132.186.221.163]) with mapi;
	Fri, 31 Aug 2012 20:25:52 +0530
From: "Yelai, Ramkumar IN BLR STS" <ramkumar.yelai@siemens.com>
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Date: Fri, 31 Aug 2012 20:25:50 +0530
Subject: Need to Iterate the record in plpgsql
Thread-Topic: Need to Iterate the record in plpgsql
Thread-Index: Ac2HiLY1h57SHMrnQyeAWoAEWW2vug==
Message-ID: 
 <13D0F6C9B3073A4999E61CAAD61AE7ECC45265BA99@INBLRK77M2MSX.in002.siemens.net>
Accept-Language: en-US
Content-Language: en-US
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
acceptlanguage: en-US
Content-Type: multipart/alternative;
	boundary="_000_13D0F6C9B3073A4999E61CAAD61AE7ECC45265BA99INBLRK77M2MSX_"
MIME-Version: 1.0
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201209/3
X-Sequence-Number: 36805

--_000_13D0F6C9B3073A4999E61CAAD61AE7ECC45265BA99INBLRK77M2MSX_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi All,

I am facing a issue in Iterating the RECORD.

The problem is, I would like to iterate the RECORD without using sql query,=
 but as per the syntax I have to use query as shown below.

FOR target IN query LOOP
    statements
END LOOP [ label ];

In my procedure, I have stored one of the procedure output as record, which=
 I am later using in another iteration. Below is the example


CREATE OR REPLACE FUNCTION test2()

Rec1 RECORD;
Rec2 RECORD;
Rec3 RECORD;

SELECT * INTO REC1 FROM test();

FOR REC2 IN ( select * from test3())
LOOP
        FOR REC3 IN REC2 --- this syntax does not allowed by Postgresql
        LOOP

        END LOOP
END LOOP

As per the example, How can I iterate pre stored record.

Please let me know if you have any suggestions.

Thanks & Regards,
Ramkumar






--_000_13D0F6C9B3073A4999E61CAAD61AE7ECC45265BA99INBLRK77M2MSX_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Exchange Server">
<!-- converted from rtf -->
<style><!-- .EmailQuote { margin-left: 1pt; padding-left: 4pt; border-left:=
 #800000 2px solid; } --></style>
</head>
<body>
<font face=3D"Verdana, sans-serif" size=3D"2">
<div>Hi All,</div>
<div>&nbsp;</div>
<div>I am facing a issue in Iterating the RECORD.</div>
<div><font face=3D"Calibri, sans-serif" size=3D"2">&nbsp;</font></div>
<div>The problem is, I would like to iterate the RECORD without using sql q=
uery, but as per the syntax I have to use query as shown below.</div>
<div>&nbsp;</div>
<div>FOR target IN query LOOP</div>
<div>&nbsp;&nbsp;&nbsp; statements</div>
<div>END LOOP [ label ];</div>
<div><font face=3D"Courier New, monospace">&nbsp;</font></div>
<div>In my procedure, I have stored one of the procedure output as record, =
which I am later using in another iteration. Below is the example</div>
<div><font face=3D"Courier New, monospace">&nbsp;</font></div>
<div><font face=3D"Courier New, monospace">&nbsp;</font></div>
<div>CREATE OR REPLACE FUNCTION test2()</div>
<div><font face=3D"Courier New, monospace">&nbsp;</font></div>
<div>Rec1 RECORD;</div>
<div>Rec2 RECORD;</div>
<div>Rec3 RECORD;</div>
<div><font face=3D"Courier New, monospace">&nbsp;</font></div>
<div>SELECT * INTO REC1 FROM test();</div>
<div>&nbsp;</div>
<div>FOR REC2 IN ( select * from test3())</div>
<div>LOOP</div>
<div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FOR REC3 IN REC2 --- this s=
yntax does not allowed by Postgresql</div>
<div><font face=3D"Courier New, monospace">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp; <font face=3D"Verdana, sans-serif">LOOP</font></font></div>
<div>&nbsp;</div>
<div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END LOOP</div>
<div>END LOOP</div>
<div><font face=3D"Calibri, sans-serif" size=3D"2">&nbsp;</font></div>
<div>As per the example, How can I iterate pre stored record.</div>
<div>&nbsp;</div>
<div>Please let me know if you have any suggestions.</div>
<div><br>

Thanks &amp; Regards,</div>
<div>Ramkumar</div>
<div><font face=3D"Calibri, sans-serif" size=3D"2">&nbsp;</font></div>
<div><font face=3D"Calibri, sans-serif" size=3D"2">&nbsp;</font></div>
<div><font face=3D"Calibri, sans-serif" size=3D"2">&nbsp;</font></div>
<div><font face=3D"Calibri, sans-serif" size=3D"2">&nbsp;</font></div>
<div><font face=3D"Calibri, sans-serif" size=3D"2">&nbsp;</font></div>
</font>
</body>
</html>

--_000_13D0F6C9B3073A4999E61CAAD61AE7ECC45265BA99INBLRK77M2MSX_--




From pavel.stehule@gmail.com Tue Sep  4 06:23:13 2012
Received: from magus.postgresql.org ([87.238.57.229])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <pavel.stehule@gmail.com>) id 1T8mXZ-0003U5-Ez
	for pgsql-sql@postgresql.org; Tue, 04 Sep 2012 06:23:13 +0000
Received: from mail-ee0-f46.google.com ([74.125.83.46])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <pavel.stehule@gmail.com>) id 1T8mXU-0005T8-U8
	for pgsql-sql@postgresql.org; Tue, 04 Sep 2012 06:23:12 +0000
Received: by eekc1 with SMTP id c1so2250973eek.19
	for <pgsql-sql@postgresql.org>; Mon, 03 Sep 2012 23:23:08 -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;
	bh=JgBBRw3o9cOQvF2U06vkhUziUWH9NoAL49LnzTUNPD8=;
	b=pnOvVZQDhxjZGKLv0oXHBKjF7l8iU+QNLKPtWtsMsxdv6ARSPwkToKSPXnJS3d3UGl
	S9NDxdACmi5g156PN37fQZ1sMXlHo+UVkS4AfIGZMmp+Cc7k95irCH+ftSNEagWv5F8f
	kuFX/aO4xxYAXva4yqxbgcc4ZJvUTMmckpc1/Nde3F7XYODwE9mZoVb57QLrQVDcm94R
	6iEKLUiGiD6VgrfEphadjT+T1VAoketLXLsDULrZOuOatOWteCewht2pgEXoFtz95qYv
	aijdsI8FeuLPYUdlvpfkCk3JXwihTq7dC+vaWKu4YYbFDMYTEy1/pxCcF0qxLU1e25Ky
	Df8Q==
Received: by 10.14.224.73 with SMTP id w49mr12594736eep.37.1346739787969; Mon,
	03 Sep 2012 23:23:07 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.14.1.7 with HTTP; Mon, 3 Sep 2012 23:22:27 -0700 (PDT)
In-Reply-To: 
 <13D0F6C9B3073A4999E61CAAD61AE7ECC45265BA99@INBLRK77M2MSX.in002.siemens.net>
References: 
 <13D0F6C9B3073A4999E61CAAD61AE7ECC45265BA99@INBLRK77M2MSX.in002.siemens.net>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Tue, 4 Sep 2012 08:22:27 +0200
Message-ID: 
 <CAFj8pRBndqKqduazpLJM6gU8uAsC73=rdmJqJ3nSJASRzZP7OA@mail.gmail.com>
Subject: Re: Need to Iterate the record in plpgsql
To: "Yelai, Ramkumar IN BLR STS" <ramkumar.yelai@siemens.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Content-Type: text/plain; charset=UTF-8
X-Pg-Spam-Score: -2.7 (--)
X-Archive-Number: 201209/4
X-Sequence-Number: 36806

Hello

http://okbob.blogspot.cz/2010/12/iteration-over-record-in-plpgsql.html
http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#Iteration_over_RECORD_variable_inside_trigger

Regards

Pavel Stehule

2012/8/31 Yelai, Ramkumar IN BLR STS <ramkumar.yelai@siemens.com>:
> Hi All,
>
> I am facing a issue in Iterating the RECORD.
>
> The problem is, I would like to iterate the RECORD without using sql query,
> but as per the syntax I have to use query as shown below.
>
> FOR target IN query LOOP
>     statements
> END LOOP [ label ];
>
> In my procedure, I have stored one of the procedure output as record, which
> I am later using in another iteration. Below is the example
>
>
> CREATE OR REPLACE FUNCTION test2()
>
> Rec1 RECORD;
> Rec2 RECORD;
> Rec3 RECORD;
>
> SELECT * INTO REC1 FROM test();
>
> FOR REC2 IN ( select * from test3())
> LOOP
>         FOR REC3 IN REC2 --- this syntax does not allowed by Postgresql
>         LOOP
>
>         END LOOP
> END LOOP
>
> As per the example, How can I iterate pre stored record.
>
> Please let me know if you have any suggestions.
>
> Thanks & Regards,
> Ramkumar
>
>
>
>
>




From ramkumar.yelai@siemens.com Tue Sep  4 09:31:55 2012
Received: from magus.postgresql.org ([87.238.57.229])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ramkumar.yelai@siemens.com>) id 1T8pUB-0003k3-F3
	for pgsql-sql@postgresql.org; Tue, 04 Sep 2012 09:31:55 +0000
Received: from krishna.sisl.co.in ([194.138.248.75])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ramkumar.yelai@siemens.com>) id 1T8pU3-0008K4-JQ
	for pgsql-sql@postgresql.org; Tue, 04 Sep 2012 09:31:53 +0000
Received: from kaveri.sisl.co.in (kaveri.sisl.co.in [132.186.192.4])
	by krishna.sisl.co.in (Postfix) with ESMTP id 645C013DD2B;
	Tue,  4 Sep 2012 15:35:28 +0530 (IST)
Received: from INBLRK77N2MSX.in002.siemens.net
	(inblrk77n2msx.in002.siemens.net [132.186.221.163])
	by kaveri.sisl.co.in (Postfix) with ESMTP id 935AA3E65DF;
	Tue,  4 Sep 2012 14:40:53 +0530 (IST)
Received: from INBLRK77M2MSX.in002.siemens.net ([169.254.2.42]) by
	INBLRK77N2MSX.in002.siemens.net ([132.186.221.163]) with mapi;
	Tue, 4 Sep 2012 15:01:13 +0530
From: "Yelai, Ramkumar IN BLR STS" <ramkumar.yelai@siemens.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
CC: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Date: Tue, 4 Sep 2012 15:01:10 +0530
Subject: Re: Need to Iterate the record in plpgsql
Thread-Topic: [SQL] Need to Iterate the record in plpgsql
Thread-Index: Ac2KZc5lHh3spemnQ4KqgyEvVXmn2wAGTweQ
Message-ID: 
 <13D0F6C9B3073A4999E61CAAD61AE7ECC4526E257D@INBLRK77M2MSX.in002.siemens.net>
References: 
 <13D0F6C9B3073A4999E61CAAD61AE7ECC45265BA99@INBLRK77M2MSX.in002.siemens.net>
	<CAFj8pRBndqKqduazpLJM6gU8uAsC73=rdmJqJ3nSJASRzZP7OA@mail.gmail.com>
In-Reply-To: 
 <CAFj8pRBndqKqduazpLJM6gU8uAsC73=rdmJqJ3nSJASRzZP7OA@mail.gmail.com>
Accept-Language: en-US
Content-Language: en-US
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
acceptlanguage: en-US
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: base64
MIME-Version: 1.0
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201209/10
X-Sequence-Number: 36812

SGkgUGF2ZWwsDQoNClRoYW5rcyBmb3IgdGhlIGxpbmtzLCBidXQgZHVlIHRvIHRoZSBwcm9qZWN0
IHNjaGVkdWxlcyBJIGhhdmUgY2hhbmdlZCBwcm9jZWR1cmUgbG9naWMgdG8gdXNlIGFycmF5IGlu
IGl0ZXJhdGlvbiBpbnN0ZWFkIG9mIHJlY29yZHMuDQoNCkhvd2V2ZXIsIHRoZSB0cmlnZ2VyIGxv
Z2ljIGlzIHJlYWxseSBnb29kIGJ1dCBpdCBzZWVtcyBzbG93IGFzIHlvdSBzYWlkLiBUaGUgYmxv
ZyBpcyByZWFsbHkgZ29vZCBzb3VyY2UgdG8gdW5kZXJzdGFuZCBwbHBnc3FsIGluIGJldHRlciB3
YXkuDQoNClRoYW5rcyAmIFJlZ2FyZHMsDQpSYW1rdW1hcg0KDQotLS0tLU9yaWdpbmFsIE1lc3Nh
Z2UtLS0tLQ0KRnJvbTogUGF2ZWwgU3RlaHVsZSBbbWFpbHRvOnBhdmVsLnN0ZWh1bGVAZ21haWwu
Y29tXSANClNlbnQ6IFR1ZXNkYXksIFNlcHRlbWJlciAwNCwgMjAxMiAxMTo1MiBBTQ0KVG86IFll
bGFpLCBSYW1rdW1hciBJTiBCTFIgU1RTDQpDYzogcGdzcWwtc3FsQHBvc3RncmVzcWwub3JnDQpT
dWJqZWN0OiBSZTogW1NRTF0gTmVlZCB0byBJdGVyYXRlIHRoZSByZWNvcmQgaW4gcGxwZ3NxbA0K
DQpIZWxsbw0KDQpodHRwOi8vb2tib2IuYmxvZ3Nwb3QuY3ovMjAxMC8xMi9pdGVyYXRpb24tb3Zl
ci1yZWNvcmQtaW4tcGxwZ3NxbC5odG1sDQpodHRwOi8vcG9zdGdyZXMuY3ovd2lraS9Qb3N0Z3Jl
U1FMX1NRTF9Ucmlja3MjSXRlcmF0aW9uX292ZXJfUkVDT1JEX3ZhcmlhYmxlX2luc2lkZV90cmln
Z2VyDQoNClJlZ2FyZHMNCg0KUGF2ZWwgU3RlaHVsZQ0KDQoyMDEyLzgvMzEgWWVsYWksIFJhbWt1
bWFyIElOIEJMUiBTVFMgPHJhbWt1bWFyLnllbGFpQHNpZW1lbnMuY29tPjoNCj4gSGkgQWxsLA0K
Pg0KPiBJIGFtIGZhY2luZyBhIGlzc3VlIGluIEl0ZXJhdGluZyB0aGUgUkVDT1JELg0KPg0KPiBU
aGUgcHJvYmxlbSBpcywgSSB3b3VsZCBsaWtlIHRvIGl0ZXJhdGUgdGhlIFJFQ09SRCB3aXRob3V0
IHVzaW5nIHNxbCANCj4gcXVlcnksIGJ1dCBhcyBwZXIgdGhlIHN5bnRheCBJIGhhdmUgdG8gdXNl
IHF1ZXJ5IGFzIHNob3duIGJlbG93Lg0KPg0KPiBGT1IgdGFyZ2V0IElOIHF1ZXJ5IExPT1ANCj4g
ICAgIHN0YXRlbWVudHMNCj4gRU5EIExPT1AgWyBsYWJlbCBdOw0KPg0KPiBJbiBteSBwcm9jZWR1
cmUsIEkgaGF2ZSBzdG9yZWQgb25lIG9mIHRoZSBwcm9jZWR1cmUgb3V0cHV0IGFzIHJlY29yZCwg
DQo+IHdoaWNoIEkgYW0gbGF0ZXIgdXNpbmcgaW4gYW5vdGhlciBpdGVyYXRpb24uIEJlbG93IGlz
IHRoZSBleGFtcGxlDQo+DQo+DQo+IENSRUFURSBPUiBSRVBMQUNFIEZVTkNUSU9OIHRlc3QyKCkN
Cj4NCj4gUmVjMSBSRUNPUkQ7DQo+IFJlYzIgUkVDT1JEOw0KPiBSZWMzIFJFQ09SRDsNCj4NCj4g
U0VMRUNUICogSU5UTyBSRUMxIEZST00gdGVzdCgpOw0KPg0KPiBGT1IgUkVDMiBJTiAoIHNlbGVj
dCAqIGZyb20gdGVzdDMoKSkNCj4gTE9PUA0KPiAgICAgICAgIEZPUiBSRUMzIElOIFJFQzIgLS0t
IHRoaXMgc3ludGF4IGRvZXMgbm90IGFsbG93ZWQgYnkgUG9zdGdyZXNxbA0KPiAgICAgICAgIExP
T1ANCj4NCj4gICAgICAgICBFTkQgTE9PUA0KPiBFTkQgTE9PUA0KPg0KPiBBcyBwZXIgdGhlIGV4
YW1wbGUsIEhvdyBjYW4gSSBpdGVyYXRlIHByZSBzdG9yZWQgcmVjb3JkLg0KPg0KPiBQbGVhc2Ug
bGV0IG1lIGtub3cgaWYgeW91IGhhdmUgYW55IHN1Z2dlc3Rpb25zLg0KPg0KPiBUaGFua3MgJiBS
ZWdhcmRzLA0KPiBSYW1rdW1hcg0KPg0KPg0KPg0KPg0KPg0K




From sergey.konoplev@postgresql-consulting.com Tue Sep  4 09:57:15 2012
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <gray.ru@gmail.com>) id 1T8psh-00025d-Gc
	for pgsql-sql@postgresql.org; Tue, 04 Sep 2012 09:57:15 +0000
Received: from mail-ob0-f174.google.com ([209.85.214.174])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <gray.ru@gmail.com>) id 1T8psf-0000Fq-75
	for pgsql-sql@postgresql.org; Tue, 04 Sep 2012 09:57:15 +0000
Received: by obbuo13 with SMTP id uo13so10583765obb.19
	for <pgsql-sql@postgresql.org>; Tue, 04 Sep 2012 02:57:12 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:sender:in-reply-to:references:from:date
	:x-google-sender-auth:message-id:subject:to:cc:content-type;
	bh=xyLALDhpkGjSVSOJim5rmWE13vHsK5uSw/MVA2/u1rU=;
	b=gAGvPScdb3VBxLFdvmWMvU7uoCKWEl0d9dvBTlJ7XNQFuT9l/J19dKQqV1/gw5pPQ+
	/mmy6SQ2ZqkUkjoSynQmpsSytRNmsSeR0uMecC1sqACTBYh/Ue5ypifBbp2pzT20XbUA
	lo2P04sVHZeD0zCdsNqKGg0HSUrb7WH28l/FQBSfM8X7H0ai/JwgpsTz+URfpc02Xo6f
	0bOSK6YNyRt64LpiYafu8CXT0lCSSU7FNGOR+7JBJdvHaKz8t7QpPDx9ZUHbrvjorpH7
	PLMts9Y0Cvl+v9U+bgv2pg+mkftUBwIbimB/7VONxuVJyyegzQ/i8wZoYqvFyGC3cB/R
	WnGA==
Received: by 10.60.26.134 with SMTP id l6mr15804882oeg.40.1346752632214; Tue,
	04 Sep 2012 02:57:12 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.182.31.163 with HTTP; Tue, 4 Sep 2012 02:56:49 -0700 (PDT)
In-Reply-To: 
 <13D0F6C9B3073A4999E61CAAD61AE7ECC45265BA99@INBLRK77M2MSX.in002.siemens.net>
References: 
 <13D0F6C9B3073A4999E61CAAD61AE7ECC45265BA99@INBLRK77M2MSX.in002.siemens.net>
From: Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
Date: Tue, 4 Sep 2012 13:56:49 +0400
X-Google-Sender-Auth: PL-5fSfpeenLhsS2RUaXr0PrpA4
Message-ID: 
 <CAL_0b1tf4BZXX2+x+mYVD6Nfju5_M0mxipXR6oQgB3YTGNx+Jg@mail.gmail.com>
Subject: Re: Need to Iterate the record in plpgsql
To: "Yelai, Ramkumar IN BLR STS" <ramkumar.yelai@siemens.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Content-Type: text/plain; charset=ISO-8859-1
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201209/5
X-Sequence-Number: 36807

If you do not need information about column types you can use hstore
for this purpose.

[local]:5432 grayhemp@grayhemp=# select * from r limit 1;
 a | b | c
---+---+---
 1 | 2 | 3
(1 row)

[local]:5432 grayhemp@grayhemp=# select * from each((select hstore(r)
from r limit 1));
 key | value
-----+-------
 a   | 1
 b   | 2
 c   | 3
(3 rows)

The key and value columns here of the text type.

On Fri, Aug 31, 2012 at 6:55 PM, Yelai, Ramkumar IN BLR STS
<ramkumar.yelai@siemens.com> wrote:
> Hi All,
>
> I am facing a issue in Iterating the RECORD.
>
> The problem is, I would like to iterate the RECORD without using sql query,
> but as per the syntax I have to use query as shown below.
>
> FOR target IN query LOOP
>     statements
> END LOOP [ label ];
>
> In my procedure, I have stored one of the procedure output as record, which
> I am later using in another iteration. Below is the example
>
>
> CREATE OR REPLACE FUNCTION test2()
>
> Rec1 RECORD;
> Rec2 RECORD;
> Rec3 RECORD;
>
> SELECT * INTO REC1 FROM test();
>
> FOR REC2 IN ( select * from test3())
> LOOP
>         FOR REC3 IN REC2 --- this syntax does not allowed by Postgresql
>         LOOP
>
>         END LOOP
> END LOOP
>
> As per the example, How can I iterate pre stored record.
>
> Please let me know if you have any suggestions.
>
> Thanks & Regards,
> Ramkumar
>
>
>
>
>



-- 
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204




From ramkumar.yelai@siemens.com Thu Sep  6 12:23:47 2012
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ramkumar.yelai@siemens.com>) id 1T9b7b-0000Io-GG
	for pgsql-sql@postgresql.org; Thu, 06 Sep 2012 12:23:47 +0000
Received: from krishna.sisl.co.in ([194.138.248.75])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ramkumar.yelai@siemens.com>) id 1T9b7T-0003rM-FM
	for pgsql-sql@postgresql.org; Thu, 06 Sep 2012 12:23:46 +0000
Received: from kaveri.sisl.co.in (kaveri.sisl.co.in [132.186.192.4])
	by krishna.sisl.co.in (Postfix) with ESMTP id BB28C13DD3B;
	Thu,  6 Sep 2012 18:27:42 +0530 (IST)
Received: from INBLRK77N2MSX.in002.siemens.net
	(inblrk77n2msx.in002.siemens.net [132.186.221.163])
	by kaveri.sisl.co.in (Postfix) with ESMTP id D5C6D5D51F2;
	Thu,  6 Sep 2012 17:32:32 +0530 (IST)
Received: from INBLRK77M2MSX.in002.siemens.net ([169.254.2.42]) by
	INBLRK77N2MSX.in002.siemens.net ([132.186.221.163]) with mapi;
	Thu, 6 Sep 2012 17:53:10 +0530
From: "Yelai, Ramkumar IN BLR STS" <ramkumar.yelai@siemens.com>
To: Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
CC: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Date: Thu, 6 Sep 2012 17:53:08 +0530
Subject: Re: Need to Iterate the record in plpgsql
Thread-Topic: [SQL] Need to Iterate the record in plpgsql
Thread-Index: Ac2Kg7J6+0wQIgBuSsyarZMm4PSpogBphvZQ
Message-ID: 
 <13D0F6C9B3073A4999E61CAAD61AE7ECC45271D26B@INBLRK77M2MSX.in002.siemens.net>
References: 
 <13D0F6C9B3073A4999E61CAAD61AE7ECC45265BA99@INBLRK77M2MSX.in002.siemens.net>
	<CAL_0b1tf4BZXX2+x+mYVD6Nfju5_M0mxipXR6oQgB3YTGNx+Jg@mail.gmail.com>
In-Reply-To: 
 <CAL_0b1tf4BZXX2+x+mYVD6Nfju5_M0mxipXR6oQgB3YTGNx+Jg@mail.gmail.com>
Accept-Language: en-US
Content-Language: en-US
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
acceptlanguage: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201209/11
X-Sequence-Number: 36813

Thanks Sergey,

I need to know about the coloumn types for the internal calculation, hence =
I am unable to use this.

Since I am a new to RDBMS and Postgresql, I am understanding your concepts =
and thinking to use this code in my project.

Thanks & Regards,
Ramkumar.

-----Original Message-----
From: gray.ru@gmail.com [mailto:gray.ru@gmail.com] On Behalf Of Sergey Kono=
plev
Sent: Tuesday, September 04, 2012 3:27 PM
To: Yelai, Ramkumar IN BLR STS
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Need to Iterate the record in plpgsql

If you do not need information about column types you can use hstore for th=
is purpose.

[local]:5432 grayhemp@grayhemp=3D# select * from r limit 1;  a | b | c
---+---+---
 1 | 2 | 3
(1 row)

[local]:5432 grayhemp@grayhemp=3D# select * from each((select hstore(r) fro=
m r limit 1));  key | value
-----+-------
 a   | 1
 b   | 2
 c   | 3
(3 rows)

The key and value columns here of the text type.

On Fri, Aug 31, 2012 at 6:55 PM, Yelai, Ramkumar IN BLR STS <ramkumar.yelai=
@siemens.com> wrote:
> Hi All,
>
> I am facing a issue in Iterating the RECORD.
>
> The problem is, I would like to iterate the RECORD without using sql=20
> query, but as per the syntax I have to use query as shown below.
>
> FOR target IN query LOOP
>     statements
> END LOOP [ label ];
>
> In my procedure, I have stored one of the procedure output as record,=20
> which I am later using in another iteration. Below is the example
>
>
> CREATE OR REPLACE FUNCTION test2()
>
> Rec1 RECORD;
> Rec2 RECORD;
> Rec3 RECORD;
>
> SELECT * INTO REC1 FROM test();
>
> FOR REC2 IN ( select * from test3())
> LOOP
>         FOR REC3 IN REC2 --- this syntax does not allowed by Postgresql
>         LOOP
>
>         END LOOP
> END LOOP
>
> As per the example, How can I iterate pre stored record.
>
> Please let me know if you have any suggestions.
>
> Thanks & Regards,
> Ramkumar
>
>
>
>
>



--
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com http:=
//www.postgresql-consulting.com

Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204




From polobo@yahoo.com Thu Sep  6 13:58:29 2012
Received: from magus.postgresql.org ([87.238.57.229])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <polobo@yahoo.com>) id 1T9cbF-0003D0-PP
	for pgsql-sql@postgresql.org; Thu, 06 Sep 2012 13:58:29 +0000
Received: from smtp103.prem.mail.ac4.yahoo.com ([76.13.13.42])
	by magus.postgresql.org with smtp (Exim 4.72)
	(envelope-from <polobo@yahoo.com>) id 1T9cbC-0005FG-4L
	for pgsql-sql@postgresql.org; Thu, 06 Sep 2012 13:58:29 +0000
Received: (qmail 84416 invoked from network); 6 Sep 2012 13:58:22 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:Cc:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:Content-Transfer-Encoding:X-Mailer:Thread-Index:Content-Language;
	b=0CNfy06m1FRxIdUV6nObzv9byqndg2L3EGW3AsRIJPZrF3WyMWJ324IdYmtTKELjhTCnsTQORZm2xBDhZE1AJpLV5AufOjPtM9W+/CWY9s9oHWiL94kYJLBwA8ujcjI88/TJEHz9+IEkbW90P9JlwCzFyE3kkguKQ4Ltz94bz0E=
	;
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024;
	t=1346939902; bh=O9xQOSe+IBaVwGI4WyDgC/gxSUv1ViSIs0sd1PR7I6g=;
	h=X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:Cc:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:Content-Transfer-Encoding:X-Mailer:Thread-Index:Content-Language;
	b=rvRomhonevzlAxOD2QIs4ilNxQq6Mgbk4OJD6oZOwoznqvE/TwQb7l4/pcDn3sINYEffotfw/pjg9rUlhqXClDLIOApT+s3EtQ2f1qR3YmorML4+X2wL+hTtLjzERXNtQQ7hDOYqFEah1yorpPjasl8ot5AabrqKQ6+2jhO789A=
X-Yahoo-Newman-Property: ymail-3
X-YMail-OSG: ndAxPjoVM1kcxDifbcem9YtrO812CmecArWDNHGP.TekUP7
	Cq9KrwtXKBlWf5RWIOOPCxid8dXgwpUk2eBV15Lh2jNw9gfFdq9J8FBDxW3w
	kdIl8exnbNfJAk86zpocPXItfW117W_VpU4h2221EjYKScTCfexruDFZP.tg
	wMR1FoJqcm1drpNHqtvU9SjqixPmQ3kkhKfmCtrtUk8KqMniYm6HemLsqRWP
	rhxj53T4_qt1AfY4.fh99LyIYNetscL2MMs5tFtjhViR5GOImC_QqlST3lvC
	smIjz5kiwNA8gIx0wIqaJ9LXfzEhrbKYwqu3fK6fT9bPgqwPWlCvUDUroKjB
	T4.B4pWWn9flwO.ztZe9Vd6ZaLESjcveuF8gbUPnyKsIlsIGra3iMKWBDM6v
	VJZrp96ZlYa0p5gnBDvNe5Nk5lX3NI7xPhYOw
X-Yahoo-SMTP: mpGJl6eswBD2IBufoVEg0Pa8gg--
Received: from WolfDog (polobo@24.93.23.188 with login)
	by smtp103.prem.mail.ac4.yahoo.com with SMTP;
	06 Sep 2012 06:58:22 -0700 PDT
From: "David Johnston" <polobo@yahoo.com>
To: "'Yelai, Ramkumar IN BLR STS'" <ramkumar.yelai@siemens.com>,
	"'Sergey Konoplev'" <sergey.konoplev@postgresql-consulting.com>
Cc: <pgsql-sql@postgresql.org>
References: 
 <13D0F6C9B3073A4999E61CAAD61AE7ECC45265BA99@INBLRK77M2MSX.in002.siemens.net>
	<CAL_0b1tf4BZXX2+x+mYVD6Nfju5_M0mxipXR6oQgB3YTGNx+Jg@mail.gmail.com>
	<13D0F6C9B3073A4999E61CAAD61AE7ECC45271D26B@INBLRK77M2MSX.in002.siemens.net>
In-Reply-To: 
 <13D0F6C9B3073A4999E61CAAD61AE7ECC45271D26B@INBLRK77M2MSX.in002.siemens.net>
Subject: Re: Need to Iterate the record in plpgsql
Date: Thu, 6 Sep 2012 09:58:09 -0400
Message-ID: <00fe01cd8c37$a6dddc20$f4999460$@yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Outlook 14.0
Thread-Index: AQHe00jTyJ2qX2rdL3UG0tA9kWHvqADTxEskAg3ZjG6XQ7cWgA==
Content-Language: en-us
X-Pg-Spam-Score: -2.0 (--)
X-Archive-Number: 201209/12
X-Sequence-Number: 36814

Yelai,

The etiquette on this list is to place all replies either in-line (but
following the content being quoted) or at the end of the posting.

My reply is at the end.

=============By: Sergey Konoplev
If you do not need information about column types you can use hstore for
this purpose.

[local]:5432 grayhemp@grayhemp=# select * from r limit 1;  a | b | c
---+---+---
 1 | 2 | 3
(1 row)

[local]:5432 grayhemp@grayhemp=# select * from each((select hstore(r) from r
limit 1));  key | value
-----+-------
 a   | 1
 b   | 2
 c   | 3
(3 rows)

The key and value columns here of the text type.
==============================

> > Hi All,
> >
> > I am facing a issue in Iterating the RECORD.
> >
> > The problem is, I would like to iterate the RECORD without using sql
> > query, but as per the syntax I have to use query as shown below.
> >
> > FOR target IN query LOOP
> >     statements
> > END LOOP [ label ];
> >
> > In my procedure, I have stored one of the procedure output as record,
> > which I am later using in another iteration. Below is the example
> >
> >
> > CREATE OR REPLACE FUNCTION test2()
> >
> > Rec1 RECORD;
> > Rec2 RECORD;
> > Rec3 RECORD;
> >
> > SELECT * INTO REC1 FROM test();
> >
> > FOR REC2 IN ( select * from test3())
> > LOOP
> >         FOR REC3 IN REC2 --- this syntax does not allowed by Postgresql
> >         LOOP
> >
> >         END LOOP
> > END LOOP
> >
> > As per the example, How can I iterate pre stored record.
> >
> > Please let me know if you have any suggestions.
> >
> > Thanks & Regards,
> > Ramkumar
> >


This makes no sense to me.  Since REC2 is a single record from "test3()"
there are no "sub-records" to iterate over.

Re-reading the thread what you want to do is now iterate over the columns of
the record that is currently in play.

The following is theoretical:

A starting point for doing what you want would be to create a temporary
table from the results of the call to "test3()".

CREATE TEMP TABLE test3_table AS
	ON COMMIT DROP
	SELECT * FROM test3()

Now using hstore you can iterate over the columns and retrieve the name and
textual value for each.  Save the column name and lookup the corresponding
column on "test3_table" to determine the data type associated with the
value.  I do not know the specific syntax to do this but the information is
available in the database.

It helps to provide the why behind what you are trying to accomplish and
just ask whether some behavior can be accomplished or emulated.

David J.