On Tue, 2012-09-04 at 21:58 -0400, David Johnston wrote:
> On Sep 4, 2012, at 21:39, Sergio Basurto <[email protected]> wrote:
>
> > I am using regexp_matches in a function like this
> >
> > create or replace function test (v_string in text) returns varchar as
> > $$
> > declare
> > i_strings text[];
> > i_string text[];
> >
> > i_strings :=
> > regexp_matches(v_string,E'[a-zA-Z0-9:\\s\\-\\.#%]*:[A-Za-z0-9\\s\\-\\.#%]+','g');
>
> You can store a single array value into i_strings. It does not magically
> convert a multi-row result into an array. You can use ARRAY_AGG to do so or
> execute the query directly as part of the loop while using a "record"
> variable to store the current row's value(s).
>
> >
> > -- Then I use the results
> > foreach i_string slice 1 in array i_strings
> > loop
> > raise notice 'row = %',i_string;
> > end loop;
> >
> > when I run the function like this:
> >
> > select test('1:Warehouse1;2:Warehouse2;');
> >
> > postgresql complains:
> > ERROR: query "SELECT
> > regexp_matches(v_string,E'[a-zA-Z0-9:\\s\\-\\.#%]*:[A-Za-z0-9\\s\\-\\.#%]+','g')"
> > returned more than one row
> >
> > Why postgres is sending the ERROR?
> >
> > Off course I am expecting more than one row!, that's why is in a foreach
> > loop in the first place.
> >
> > If I run:
> > select
> > regexp_matches('1:Warehouse1;2:Warehouse2;',E'[a-zA-Z0-9:\\s\\-\\.#%]*:[A-Za-z0-9\\s\\-\\.#%]+','g');
> > regexp_matches
> > ----------------
> > {1:Warehouse1}
> > {2:Warehouse2}
> > (2 rows)
> >
> > I am doing something wrong?
>
> Note that because you do not use grouping in your expression there is only a
> single array "cell" in each row - but there could be more than one in which
> case your for-each above would effectively loop through each sub-component of
> the match.
>
> >
> > Regards,
> >
>
> David J.
>
Thanks for your response David, but my doubt arise because if I use this
i_strings text[] := array [[1:Warehouse1],[2:Warehouse2]];
loops without problem. Is not the same thing?
it prints:
NOTICE: row = {1:Warehouse1}
NOTICE: row = {2:Warehouse2}
