On Tue, 2012-09-04 at 21:58 -0400, David Johnston wrote: > On Sep 4, 2012, at 21:39, Sergio Basurto <sbasu...@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}