Re: Iterate over an array in stored procedure

On 6/20/07, Henrik Zagerholm <henke(at)mac(dot)se> wrote:
> Hello,
>
> I wonder how I can iterate over a sql array in a pl/perl function.
>
> I want to pass in an text[] to a function and then iterate through
> the values and run it through a perl sprintf but I can't seem to get
> it to work.
>
> Should I do a generate_series and make an internal perl array?
> If so how would I do this?

well, it's not pl/perl, but you can expand array like this:

create or replace function explode_array(in_array anyarray) returns
setof anyelement as
$$
select ($1)[s] from generate_series(1,array_upper($1, 1)) as s;
$$
language sql immutable;

There is also a undocumented internal function, _pg_expandarray, which
does something similar, iirc it's in the information_schema namespace.
Once you have it in set form, manipulation in perl should be trivial.
(there may be a better way to do this in pl/perl, i really have no
idea).

merlin