CODEWITHSUNDEEP
plpgsqlpostgresql-9.1postgresql-9.3
OKE
OKE

Reputation: 363

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

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?

Upvotes: 1

Views: 166

Answers (1)

OKE
OKE

Reputation: 363

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

Upvotes: 1

Related Questions