Re: Need to Iterate the record in plpgsql

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(at)grayhemp=# select * from r limit 1; a | b | c
---+---+---
1 | 2 | 3
(1 row)

[local]:5432 grayhemp(at)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.