Integer array in plpgsql throws error in PostgreSQL 9.1 but works in 9.3

Question

I have this function:

CREATE OR REPLACE FUNCTION check_presence_row(
    _id_att_files integer[],
    _people_name character varying, 
    _people_id character varying, 
    _time_start timestamp without time zone, 
    _time_end timestamp without time zone
)
RETURNS integer[] AS $BODY$
DECLARE
    ids integer array;
BEGIN
    ids := (SELECT "id_att_file"FROM att_presence WHERE
        "id_att_file" = ANY ("_id_att_files") AND
        "id_people" = get_people(
                "_people_id",
                "_people_name") AND
        "time_start" = "_time_start" AND
        "time_end" = "_time_end");

   RETURN ids;
END;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

When i call it in PostgreSQL version 9.3 this way:

SELECT check_presence_row(
    CAST ('{1,4}' AS int[]), 
    CAST ('Random Guy' AS varchar), 
    CAST ('D0388A' AS varchar), 
    CAST ('2014/08/23 8:04:00' AS timestamp without time zone), 
    CAST ('2014/08/23 8:34:00' AS timestamp without time zone)
);

it runs OK and return result as desired, but on 9.1 when i send the same query it throws an error:

ERROR:  array value must start with "{" or dimension information
CONTEXT:  PL/pgSQL function "check_presence_row" line 5 at assignment
********** Error **********

ERROR: array value must start with "{" or dimension information
SQL state: 22P02
Context: PL/pgSQL function "check_presence_row" line 5 at assignment

I found out that when any of the input values of that array is exactly 1 it fails this way but if I change them to some different integer value the pass.... but only on my localhost. Our production server still fails to pass with any value. We tried another computer and that one pass even if the values are 1. Any ideas?

Weird issue. Do you think you can simplify it down to a sqlfiddle.com ? — Craig Ringer
– Craig Ringer, Commented Sep 25, 2014 at 14:19
Never mind, i just found why is that. It was caused by that ids := assignemnt because i had exactly one one row in att_files (thats why on my computer it failed with I 1) — OKE
– OKE, Commented Sep 25, 2014 at 14:57

OKE · Accepted Answer · 2014-09-25 16:27:44Z

False alert, I just found out that it was caused by that ids := assignment. Because I had one row with id = 1 in att_file table it went wrong on my localhost, while on another computer with empty DB does not. I changed the return to not pass that variabile but only query instead:

CREATE OR REPLACE FUNCTION check_presence_row(
    _id_att_files integer[], 
    _people_name character varying, 
    _people_id character varying, 
    _time_start timestamp without time zone, 
    _time_end timestamp without time zone
)
RETURNS SETOF integer AS
$BODY$
BEGIN
    RETURN QUERY SELECT "id_att_file"FROM att_presence WHERE
        "id_att_file" = ANY ("_id_att_files") AND
        "id_people" = get_people(
            "_people_id",
            "_people_name") AND
        "time_start" = "_time_start" AND
        "time_end" = "_time_end";
END;$BODY$
LANGUAGE plpgsql

Collectives™ on Stack Overflow

Integer array in plpgsql throws error in PostgreSQL 9.1 but works in 9.3

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related