Thread: BUG #7520: regexp_matches does not work as expected

The following bug has been logged on the website:

Bug reference:      7520
Logged by:          Sergio Basurto
Email address:      sbasurto@soft-gator.com
PostgreSQL version: 9.1.4
Operating system:   Gentoo
Description:        =


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 :=3D
regexp_matches(v_string,E'[a-zA-Z0-9:\\s\\-\\.#%]*:[A-Za-z0-9\\s\\-\\.#%]+'=
,'g');

The I use the results =

foreach i_string slice 1 in array i_strings
loop
    raise notice 'row =3D %',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

Off course I am expecting more than one row!

If I run:
select
regexp_matches('1:Warehouse1;2:Warehouse2;',E'[a-zA-Z0-9:\\s\\-\\.#%]*:[A-Z=
a-z0-9\\s\\-\\.#%]+','g');
 regexp_matches =

----------------
 {1:Warehouse1}
 {2:Warehouse2}
(2 rows)

sbasurto@soft-gator.com writes:
> 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');

> 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

As per David Johnston's response on pgsql-general, this isn't a bug:
simple assignments in plpgsql expect a simple source value, not a rowset.

Personally I'd just iterate over the regexp_matches result directly and
not bother trying to store it into a variable, for example

    for string_var in select regexp_matches(...) loop ...

            regards, tom lane