Loop through array parameter WITHOUT foreach

Question

CREATE OR REPLACE FUNCTION fnMyFunction(recipients recipient[]) ...

    FOREACH v_recipient IN ARRAY recipients
       LOOP
          v_total := v_total + v_recipient.amount;
          INSERT INTO tmp_recipients(id, amount)
          VALUES(v_recipient.id, v_recipient.amount::numeric(10,2));
    END LOOP;

...

This works great in dev environment, but just found out the release environment is 8.4 which doesn't appear to support the FOREACH construct. I was hoping someone might shed some light on an alternative implementation for loop though the array parameter set and using values from array in a similar fashion to avoid a complete refactor.

The error message I am receiving is:

ERROR: syntax error at or near "FOREACH" SQL state: 42601 Context: SQL statement in PL/PgSQL function "fnMyFunction" near line ##

The db environment is on a shared host so I have no options for platform upgrade.
I tagged postgres 9.1 and 8.4 because the function works properly in 9.x but fails on 8.4.

Craig Ringer · Accepted Answer · 2012-10-02 13:31:21Z

2

Use unnest; I think it was in 8.4. Untested but I think is right:

FOR v_recipient IN SELECT vr FROM unnest(recipients) x(vr)
LOOP
....
END LOOP;

If you can't do that you'll have to loop over array_length using indexing into the array.

edited Oct 2, 2012 at 13:31

answered Oct 2, 2012 at 4:46

Craig Ringer

328k83 gold badges739 silver badges818 bronze badges

So it looks like this works, unfortunately I'm not finding a clear explanation of unnest. The issue now is I seem to be getting the first item in the v_recipient element but not the 2nd. The recipient element is based on a two fields id and amount i'm assuming the structure would look something like: {{1,1.00},{2,1.00}}

Ryan Fisch
– Ryan Fisch

10/02/2012 13:27:15
Commented Oct 2, 2012 at 13:27

Add a comment |

Erwin Brandstetter · Accepted Answer · 2013-07-13 01:17:10Z

The way you have it, you execute one INSERT at a time. With relational databases, set-based operations are regularly much faster than iterating through records one at a time.

This should be simpler, faster and work with PostgreSQL 8.4 or later:

INSERT INTO tmp_recipients(id, amount)
SELECT (r.col).*
FROM   (SELECT unnest(recipients) AS col) r

This assumes that the composite base type of the array consists of (id, amount) - in that order - and the type of amount can be coerced to numeric(10,2). Else, or just to be sure, be more explicit:

INSERT INTO tmp_recipients(id, amount)
SELECT (r.col).id, (r.col).amount::numeric(10,2)
FROM   (SELECT unnest(recipients) AS col) r

The parentheses around (r.col) are not optional. They are required to disambiguate the syntax for the composite type.

Collectives™ on Stack Overflow

Loop through array parameter WITHOUT foreach

2 Answers 2

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related