Error passing array of ints to a plpgsql function (postgres 8.3.7)

Question

I have the following function, which receives an array of ints, iterates it calling other function:

CREATE FUNCTION calculateAbsencesForIDs(INT[]) RETURNS TEXT AS 
'
DECLARE
  index integer := 0;
  resultArray decimal[];
  id int;
  result text;
BEGIN
  FOR id IN SELECT $1 LOOP

        resultArray[index] = calculateAbsenceForID(id); 
        index := index + 1;

END LOOP;
RETURN array_to_string(resultArray, result);
END;
'
LANGUAGE plpgsql;

I try to call it using:

SELECT calculateAbsencesForIDs(ARRAY[85,74,75,76,77,78,79,80]);

or

SELECT calculateAbsencesForIDs('{85,74,75,76,77,78,79,80}');

or

SELECT calculateAbsencesForIDs('{85,74,75,76,77,78,79,80}'::int[]);
...

But I have always the same error:

[Error Code: 0, SQL State: 22P02]  ERROR: invalid input syntax for integer: "{85,74,75,76,77,78,79,80}"

I don't know how I can call this function. I have looked in postgres docs, I think this is correct but it doesn`t work.

Answer 1

You need to loop over the elements of the array like this:

BEGIN
  FOR i in 1 .. array_length($1, 1) LOOP

        resultArray[i] = calculateAbsenceForID($1[i]); 

  END LOOP;
  RETURN array_to_string(resultArray, result);

END;

Note that this will throw an error if $1 is NULL

Answer 2

This line:

FOR id IN SELECT $1 LOOP

means that you assign id to each value from SELECT $1 which returns a single record with a single field of type INT[].

As id is declared as INT, you get the conversion error you're observing.

In 8.4 and above you could use UNNEST, in 8.3 replace it with

FOR id IN
SELECT  $1[i]
FROM    generate_series(1, ARRAY_UPPER($1, 1)) i
LOOP

Alternatively, you could just do:

SELECT  ARRAY_TO_STRING
                (
                ARRAY
                (
                SELECT  calculateAbsenceForID($1[i])
                FROM    generate_series(1, ARRAY_UPPER($1, 1)) i
                )
                )