On 6/20/07, Henrik Zagerholm <henke@xxxxxx> 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