BUG #7520: regexp_matches does not work as expected

Started by Sergio Basurtoover 13 years ago2 messagesbugs
Jump to latest
#1Sergio Basurto
sbasurto@soft-gator.com

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 :=
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 = %',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-Za-z0-9\\s\\-\\.#%]+','g');
regexp_matches
----------------
{1:Warehouse1}
{2:Warehouse2}
(2 rows)

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Sergio Basurto (#1)
Re: BUG #7520: regexp_matches does not work as expected

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