8

I'm trying to loop through few fields and run a function on them:

FOR field IN ARRAY['f1','f2'] LOOP
    execute pg_temp.converFieldToLower(newTableNameRaw,field)
END LOOP;

This is the function i'm trying to use:

CREATE OR REPLACE FUNCTION pg_temp.converFieldToLower(t varchar, f varchar) RETURNS void AS $$
#variable_conflict use_variable
BEGIN
  EXECUTE concat_ws (' ', 'UPDATE',t,'SET',f,'= LOWER(',f,')');
END;
$$ LANGUAGE plpgsql;

It looks like it's not the right way to declare an array, what am I doing wrong?

ERROR:  syntax error at or near "ARRAY"
LINE 49:         FOR field IN ARRAY['f1','f2'] LOOP
Guy s
  • 1,358
  • 3
  • 15
  • 25
  • You write *"fields"*, but demonstrate *constant values*. So: Constants, plpgsql parameters or variables, or fields of a table? And is the input an actual array? And do you want to discard any results like demonstrated or assign results to targets? The best solution depends on details of your situation. Best provide a complete (as simple as possible) plpgsql function showing involved data types, function parameters, etc. – Erwin Brandstetter Feb 16 '17 at 23:04
  • I'm use constant text values as i demonstart. – Guy s Feb 19 '17 at 06:50

3 Answers3

11

The FOREACH loop is designed specifically for iterating through the elements of an array value, e.g.:

FOREACH field IN ARRAY ARRAY['f1','f2'] LOOP
    execute pg_temp.converFieldToLower(newTableNameRaw,field) into res;
END LOOP;

The feature was introduced in Postgres 9.1.

klin
  • 99,138
  • 12
  • 177
  • 203
5

EXECUTE is for dynamic SQL and there is nothing dynamic here.
You probably meant to use PERFORM to discard results.

There is FOREACH for looping, like @klin already provided. More details:

Or just use plain SQL instead, often simpler and faster:

PERFORM pg_temp.converFieldToLower(newTableNameRaw, t.val)
FROM   (VALUES ('f1'), ('f2')) t(val);  -- for some given constants or variables

The best solution depends on details of the situation. FOREACH may be a good idea for dealing with actual arrays.

For only two or three constants or variables I would just spell it out and avoid any overhead. Simpler, faster.

PERFORM pg_temp.converFieldToLower(newTableNameRaw, 'f1');
PERFORM pg_temp.converFieldToLower(newTableNameRaw, 'f2');
Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
1

You want to unnest the array.

FOR field IN unnest(ARRAY['f1','f2']) LOOP
    execute pg_temp.converFieldToLower(newTableNameRaw,field)
END LOOP;
Guy s
  • 1,358
  • 3
  • 15
  • 25
edruid
  • 683
  • 4
  • 12