Re: regexp_matches question SOLVED

On Wed, 2012-09-05 at 21:15 -0400, David Johnston wrote:
> 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.

Thank you David for all your help,

I got it finally thanks your explanation, so the code that works for me
is:

create or replace function test (v_string in text) returns varchar
as $$
declare
i_strings refcursor := null;
i_string text[];
i_query text;

begin

i_query := 'select regexp_matches('''||v_string||''',E''[a-zA-Z0-9:\
\s\\-\\.#%]*:[A-Za-z0-9\\s\\-\\.#%]+'',''g'')';
open i_strings for execute i_query;

if i_strings is not null then
loop fetch i_strings into i_string;
exit when not found;
raise notice 'row =
%',i_string;
end loop;
close i_strings;
end if;
return 0;
end;
$$ LANGUAGE plpgsql;

Thanks again David.

Kind Regards,