Re: regexp_matches question

On Sep 5, 2012, at 19:02, Sergio Basurto <sbasurto(at)soft-gator(dot)com> wrote:

> On Tue, 2012-09-04 at 21:58 -0400, David Johnston wrote:
>>
>> On Sep 4, 2012, at 21:39, Sergio Basurto <sbasurto(at)soft-gator(dot)com> 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}

A 2-dimensional array is not the same as a set of 1-dimensional arrays.

David J.