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 ... — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 18, 2013 at 2:03

Quassnoi · Accepted Answer · 2013-01-17 11:22:45Z

1

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

answered Jan 17, 2013 at 11:22

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

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

2 Comments

user330315 Over a year ago

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)

Quassnoi Over a year ago

@a_horse_with_no_name: yes, probably. Post it as an anwer, I'll upvote.

Richard D · Accepted Answer · 2013-08-03 00:34:11Z

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 3, 2013 at 0:34

Richard D

3273 silver badges16 bronze badges

answered Jan 17, 2013 at 12:33

user330315

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related