Loop each array's item Postgresql

Question

I have a function in which I want to loop throw each array's item. I get a string in input like 'tab1#tab2#tab3'...Each item of the string must be splitted (by #) in order to obtain tab1, tab2, tab3 into myArray. My function is:

CREATE OR REPLACE FUNCTION funcA(
myUid integer,
mytable_name varchar,
state varchar)
RETURNS void AS

$BODY$


declare

TABarray varchar[];
indx int;

BEGIN  


    select REGEXP_REPLACE('{'||myTABLE_NAME||'}','#','','g') into TABarray;


    for indx in 1..array_length(TABarray, 1) loop

    execute 'update ' || TABarray(indx) || ' set CODE_STATO = ''' || state || ''' where uid = ' ||  myUid || 'and CODE_STATO <> ''N'' ';
    raise notice 'i: %', TABarray[ indx ];

    end loop;

    END; $BODY$
    LANGUAGE plpgsql stable

As a result I expect 3 splitted string such as:

-tab1

-tab2

-tab3

Right now myFunction print {tab1tab2tab3}.

   select oms_write_stato (10, 'tab1#tab2#tab3', '')

What I am doing wrong?

Thank you in advance!

Is there any reason for the function? Do you want to use `regexp_split_to_table` instead? https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=72513c214fc9d8420ffd12d166667657 — S-Man, Oct 05 '18 at 15:35
In order, I want to update tab1 then tab2 then tab3. This is the reason I want to loop throw each item. — Emanuele Antico, Oct 05 '18 at 15:46
How does this even work with `TABarray(indx)`??? And I don't understand what is or isn't happening in your question. — 404, Oct 05 '18 at 15:56

score 1 · Accepted Answer · answered Oct 05 '18 at 16:04

You could use string_to_array to split the string into array. Also, you were using () to refer to index elements instead of []

CREATE OR replace FUNCTION funca( myuid integer, mytable_name varchar, state varchar) 
    returns void AS 
    $BODY$
    DECLARE 
    tabarray VARCHAR[];
    indx    int;
    BEGIN 
      SELECT string_to_array(mytable_name ,'#') 
      INTO   tabarray; 
      for indx IN 1..array_length(tabarray, 1) 
      LOOP 
     --check the o/p of this notice below to see if update statement is correct
     --raise notice '%', 'update ' || tabarray[indx] || ' set CODE_STATO = ''' || state || ''' where uid = ' || myuid || 'and CODE_STATO <> ''N'' ';
        execute 'update ' || tabarray[indx] || ' set CODE_STATO = ''' || state || ''' where uid = ' ||  myUid || ' and CODE_STATO <> ''N'' ';
      raise notice 'i: %', tabarray[ indx ]; 
    END LOOP;
    END;
 $BODY$ language plpgsql stable;

@EmanueleAntico : You're welcome. One thing I did not modify / mention in my answer is that you should prefer the `using` clause instead of concatenation in dynamic execute: https://stackoverflow.com/a/2107520/7998591 — Kaushik Nayak, Oct 05 '18 at 16:11

Pavel Stehule · Answer 2 · 2018-10-06T06:56:21.363

PL/pgSQL has FOREACH IN ARRAY statement for this purpose:

You task can be written some like:

-- Don't use case mixed identifiers (prohibit camel notation)
create or replace function funca(uid integer,
                                 tablenames varchar,
                                 state varchar)
returns void as $$
declare tablename text;
begin
  foreach tablename in array string_to_array(tablenames, '#')
  loop
    execute format('update %I set code_stato = $1 where uid = $2 and code_state <>'N',
                   tablename)
      using state, uid;
  end loop;
end;
$$ language plpgsql;

Notes:

don't mix upper lower chars in identifiers
don't mix upper / lower keywords - there are some variants - keywords by upper cases, or all by lower cases, but mix is bad for reading
when you use dynamic SQL, then sanitize your data before you use it in dynamic query - use quote_ident,quote_literal functions, or function format with secure placeholders and when it is possible, pass with USING clause.
postgres has array types - using str1#str2#str3#str4 is little bit obscure in Postgres - use native arrays like ARRAY['str1','str2','str3','str4'].

Loop each array's item Postgresql

2 Answers2