pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed to Iterate the record in plpgsql
6+ messages / 4 participants
[nested] [flat]
* Need to Iterate the record in plpgsql
@ 2012-08-31 14:55 Yelai, Ramkumar IN BLR STS <ramkumar.yelai@siemens.com>
0 siblings, 2 replies; 6+ messages in thread
From: Yelai, Ramkumar IN BLR STS @ 2012-08-31 14:55 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need to Iterate the record in plpgsql
@ 2012-09-04 06:22 Pavel Stehule <pavel.stehule@gmail.com>
parent: Yelai, Ramkumar IN BLR STS <ramkumar.yelai@siemens.com>
1 sibling, 1 reply; 6+ messages in thread
From: Pavel Stehule @ 2012-09-04 06:22 UTC (permalink / raw)
To: Yelai, Ramkumar IN BLR STS <ramkumar.yelai@siemens.com>; +Cc: pgsql-sql
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
>
>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need to Iterate the record in plpgsql
@ 2012-09-04 09:31 Yelai, Ramkumar IN BLR STS <ramkumar.yelai@siemens.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Yelai, Ramkumar IN BLR STS @ 2012-09-04 09:31 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-sql
Hi Pavel,
Thanks for the links, but due to the project schedules I have changed procedure logic to use array in iteration instead of records.
However, the trigger logic is really good but it seems slow as you said. The blog is really good source to understand plpgsql in better way.
Thanks & Regards,
Ramkumar
-----Original Message-----
From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: Tuesday, September 04, 2012 11:52 AM
To: Yelai, Ramkumar IN BLR STS
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Need to Iterate the record in plpgsql
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
>
>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need to Iterate the record in plpgsql
@ 2012-09-04 09:56 Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
parent: Yelai, Ramkumar IN BLR STS <ramkumar.yelai@siemens.com>
1 sibling, 1 reply; 6+ messages in thread
From: Sergey Konoplev @ 2012-09-04 09:56 UTC (permalink / raw)
To: Yelai, Ramkumar IN BLR STS <ramkumar.yelai@siemens.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need to Iterate the record in plpgsql
@ 2012-09-06 12:23 Yelai, Ramkumar IN BLR STS <ramkumar.yelai@siemens.com>
parent: Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
0 siblings, 1 reply; 6+ messages in thread
From: Yelai, Ramkumar IN BLR STS @ 2012-09-06 12:23 UTC (permalink / raw)
To: Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>; +Cc: pgsql-sql
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 Konoplev
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 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need to Iterate the record in plpgsql
@ 2012-09-06 13:58 David Johnston <polobo@yahoo.com>
parent: Yelai, Ramkumar IN BLR STS <ramkumar.yelai@siemens.com>
0 siblings, 0 replies; 6+ messages in thread
From: David Johnston @ 2012-09-06 13:58 UTC (permalink / raw)
To: 'Yelai, Ramkumar IN BLR STS' <ramkumar.yelai@siemens.com>; 'Sergey Konoplev' <sergey.konoplev@postgresql-consulting.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-09-06 13:58 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-08-31 14:55 Need to Iterate the record in plpgsql Yelai, Ramkumar IN BLR STS <ramkumar.yelai@siemens.com>
2012-09-04 06:22 ` Pavel Stehule <pavel.stehule@gmail.com>
2012-09-04 09:31 ` Yelai, Ramkumar IN BLR STS <ramkumar.yelai@siemens.com>
2012-09-04 09:56 ` Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
2012-09-06 12:23 ` Yelai, Ramkumar IN BLR STS <ramkumar.yelai@siemens.com>
2012-09-06 13:58 ` David Johnston <polobo@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox