Обсуждение: regexp_matches question

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');
       
-- 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?

Regards,
       

On Sep 4, 2012, at 21:39, Sergio Basurto <sbasurto@soft-gator.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.
Youcan use ARRAY_AGG to do so or execute the query directly as part of the loop while using a "record" variable to
storethe 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
thanone 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
therecould be more than one in which case your for-each above would effectively loop through each sub-component of the
match.

>
> Regards,
>

David J.

On Tue, 2012-09-04 at 21:58 -0400, David Johnston wrote:

On Sep 4, 2012, at 21:39, Sergio Basurto <sbasurto@soft-gator.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}

On Tue, 2012-09-04 at 21:58 -0400, David Johnston wrote:

On Sep 4, 2012, at 21:39, Sergio Basurto <sbasurto@soft-gator.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}

On Tue, 2012-09-04 at 21:58 -0400, David Johnston wrote:

On Sep 4, 2012, at 21:39, Sergio Basurto <sbasurto@soft-gator.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.

Thank you David for all your help,

I got it working finally thanks to 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,

On Wed, 2012-09-05 at 21:15 -0400, David Johnston wrote:

On Sep 5, 2012, at 19:02, Sergio Basurto <sbasurto@soft-gator.com> wrote:

On Tue, 2012-09-04 at 21:58 -0400, David Johnston wrote:

On Sep 4, 2012, at 21:39, Sergio Basurto <sbasurto@soft-gator.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,