Search Postgresql Archives

Re: regexp_matches question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux