Thread: How iterate records

Hi,
I have a problem of plpgsql usage. When I iterate through dynamic query, like this:

FOR my_/record/ IN EXECUTE /text_expression/ LOOP
    /statements/
END LOOP;

I am not able to construct expression, which can get value for each 'item' of my_record, something like my_record[3] or
get_item(my_record,3), without knowledge about real names of record pieces. I know that it is not the goal of this
language,but my goal is to implement some lite functions returning XML rather then something else... 

pajout

Jan Poslusny wrote:
> Hi,
> I have a problem of plpgsql usage. When I iterate through dynamic query,
> like this:
>
> FOR my_/record/ IN EXECUTE /text_expression/ LOOP    /statements/
> END LOOP;
>
> I am not able to construct expression, which can get value for each
> 'item' of my_record, something like my_record[3] or get_item(my_record,
> 3), without knowledge about real names of record pieces. I know that it
> is not the goal of this language, but my goal is to implement some lite
> functions returning XML rather then something else...

You'll need to use another language. As you say, plpgsql isn't designed
for this sort of thing, and it's not clear how you would handle
different types as you iterated over row attributes.

Try pltcl or plperl (or python I'd have thought) - they're much more
suited to this sort of thing.

PS - IMHO, formatting data into XML is a client-side task, middle-ware
probably.
--
   Richard Huxton
   Archonet Ltd

Hello,

use other languege. You need hash table, and hash are not implemented in
PL/pgSQL. Some examples
http://www.root.cz/clanky/plperl-postgresql-aplikacni-server/

but in czech language.

Pavel



On Tue, 22 Feb 2005, Jan Poslusny wrote:

> Hi,
> I have a problem of plpgsql usage. When I iterate through dynamic query, like this:
>
> FOR my_/record/ IN EXECUTE /text_expression/ LOOP
>     /statements/
> END LOOP;
>
> I am not able to construct expression, which can get value for each 'item' of my_record, something like my_record[3]
orget_item(my_record, 3), without knowledge about real names of record pieces. I know that it is not the goal of this
language,but my goal is to implement some lite functions returning XML rather then something else... 
>
> pajout
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

>
> Try pltcl or plperl (or python I'd have thought) - they're much more
> suited to this sort of thing.
>
> PS - IMHO, formatting data into XML is a client-side task, middle-ware
> probably.
> --

Its not true. There is SQL/XML, but PostgreSQL don't support this. Where
you can use it: fast generating report (XML, XSLT, CSS), fast solution for
data exchange, ...

Regards
Pavel Stehule