10

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,586
  • 3
  • 20
  • 27
  • 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

12

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
  • 112,967
  • 15
  • 204
  • 232
6

EXECUTE is for dynamic SQL and there is nothing dynamic in your loop.
You probably meant to use PERFORM to discard results. See:

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

Typically, plain SQL is simpler and faster:

PERFORM pg_temp.converFieldToLower(newTableNameRaw, t.val)
FROM    unnest('{f1,f2}'::text[]) t(val);

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

PERFORM pg_temp.converFieldToLower(newTableNameRaw, 'f1');
PERFORM pg_temp.converFieldToLower(newTableNameRaw, 'f2');

FOREACH may be a good idea for more sophisticated operations per array element.

The function you added is vulnerable for SQL injection. See:

Here is a safe variant with format():

CREATE OR REPLACE FUNCTION pg_temp.converFieldToLower(t text, f text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format(
'UPDATE %1$I
SET    %2$I =  lower(%2$I)
WHERE  %2$I <> lower(%2$I)', t, f);
END
$func$;

While being at it, I added a WHERE clause to skip update operations that wouldn't change the row - at full cost.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
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,586
  • 3
  • 20
  • 27
edruid
  • 693
  • 4
  • 14