Thread: Need to Iterate the record in plpgsql
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
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 > > > > >
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 | 1b | 2c | 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
Hi Pavel, Thanks for the links, but due to the project schedules I have changed procedure logic to use array in iteration instead ofrecords. However, the trigger logic is really good but it seems slow as you said. The blog is really good source to understand plpgsqlin 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 > > > > >
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 | 1b | 2c | 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
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 | 1b | 2c | 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 ASON COMMIT DROPSELECT * 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.