12

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
Improve this question
2
  • 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. Commented Feb 16, 2017 at 23:04
  • I'm use constant text values as i demonstart. Commented Feb 19, 2017 at 6:50

3 Answers 3

Reset to default
16

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

Comments

1

You want to unnest the array.

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

2 Comments

This should not to work - needs FOR r IN SELECT unnest. It should be significantly slower than FOREACH
EXECUTE should not be used here - see @Erwin reply

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.