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.

Starting with PostgreSQL 9.1 you can use [`FOREACH target IN ARRAY arr ...`](http://www.postgresql.org/docs/current/interactive/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY) — Erwin Brandstetter, Jan 18 '13 at 02:03

score 1 · Answer 1 · answered Jan 17 '13 at 11:22

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
                )
                )

Wouldn't a for loop like this: `for i in 1 .. array_length($1, 1) loop` be better? (and use `$1[i]` inside the loop) — , Jan 17 '13 at 12:16
@a_horse_with_no_name: yes, probably. Post it as an anwer, I'll upvote. — Quassnoi, Jan 17 '13 at 12:21

score 1 · Answer 2 · edited Aug 03 '13 at 00:34

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

edited Aug 03 '13 at 00:34

Richard D

327
3
16

answered Jan 17 '13 at 12:33

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

2 Answers2