up vote 2 down vote favorite

I am trying to select arrays in a set-returning function in postgres 8.4, but receiving the error:

"array value must start with "{" or dimension information".

This issue appears to relate to locationnodes.rs_people_c sometimes having an empty array. I've tried to get around that with a COALESCE statement. No luck.

function:

CREATE OR REPLACE FUNCTION fn_matview_location_slots (
    week_start  DATE
) RETURNS setof matview_location_slots_info AS
$$
DECLARE
    resulter    matview_location_slots_info%ROWTYPE;
BEGIN
    FOR resulter IN
        SELECT
            rs_node               AS node,
            rs_date               AS dater,
            ...
            COALESCE(rs_people_c, '{}'::INTEGER[]) AS people,
            rs_location           AS location
        FROM
            locationnodes
        WHERE
            rs_date >= week_start
    LOOP
        RETURN NEXT resulter;
    END LOOP;
END; $$ LANGUAGE plpgsql;

type:

CREATE TYPE matview_location_slots_info AS (
        node              VARCHAR,
        dater             DATE,
        ...
        people            INTEGER[],
        location          INTEGER[]
);

data

select rs_people_c from locationnodes;
           rs_people_c
-------------------------------------
 {}
 {}
 {731}
 {32}
 {31}
 {}
 {62}
 {540,72,69,53,37,42,201,51,58}
 {64}
share|improve this question
What version you have ? I checked that on 9.0.4 and it works well (without COALESCE too). Also you have typo in your PL/pgSQL function week_start DATE, should be just week_start DATE. – Grzegorz Szpetkowski Aug 2 '11 at 21:21
@Grzegorz : thanks for the notes. I've edited the example above, which is part of a larger function to fix the week_start comma problem. Thanks for pointing that out. I've also added the postgres version (8.4). – rorycl Aug 2 '11 at 21:34
feedback

2 Answers

up vote 1 down vote accepted

I made a stupid type definition error (which I excluded from my original question, but Grzegorz alluded to -- thanks Grzegorz).

I posting this as an answer for anyone who googles the problem of array value must start with "{" or dimension information.

In my case the issue was that the location return type was defined as an array of integers, but the function was returning a simple integer. Unfortunately Postgres doesn't provide more information about the specific problem in these cases.

Also note that in this sort of case you don't need to coalesce NULL arrays to {} as I did in my question.

In short : make sure your array return types are receiving array input!

share|improve this answer
feedback
up vote 1 down vote

In addition to @rorycl's answer here is working test-case under PostgreSQL 8.4.8:

DROP TYPE IF EXISTS matview_location_slots_info;
CREATE TYPE matview_location_slots_info AS
(
    node varchar,
    dater date,
    people integer[]
);
DROP TABLE IF EXISTS locationnodes;
CREATE TABLE locationnodes
(
    rs_node varchar,
    rs_date date,
    rs_people_c integer[]
);
INSERT INTO locationnodes VALUES
    ('aaa', '2011-01-01', '{}'),
    ('bbb', '2011-01-02', '{}'),
    ('ccc', '2011-01-03', '{731}'),
    ('ddd', '2011-01-04', '{32}'),
    ('eee', '2011-01-05', '{31}'),
    ('fff', '2011-01-06', '{}'),
    ('ggg', '2011-01-07', '{62}'),
    ('hhh', '2011-01-08', '{540, 72, 69, 53, 37, 42, 201, 51, 58}'),
    ('iii', '2011-01-09', '{64}');

PL/pgSQL function:

CREATE OR REPLACE FUNCTION fn_matview_location_slots (week_start date)
RETURNS setof matview_location_slots_info AS $$
DECLARE
    resulter matview_location_slots_info%ROWTYPE;
BEGIN
    FOR resulter IN
        SELECT
            rs_node AS node,
            rs_date AS dater,
            rs_people_c AS people
        FROM
            locationnodes
        WHERE
            rs_date >= week_start
    LOOP
        RETURN NEXT resulter;
    END LOOP;
END; $$ LANGUAGE plpgsql;

Result:

SELECT fn_matview_location_slots('2011-01-01');
             fn_matview_location_slots
---------------------------------------------------
 (aaa,2011-01-01,{})
 (bbb,2011-01-02,{})
 (ccc,2011-01-03,{731})
 (ddd,2011-01-04,{32})
 (eee,2011-01-05,{31})
 (fff,2011-01-06,{})
 (ggg,2011-01-07,{62})
 (hhh,2011-01-08,"{540,72,69,53,37,42,201,51,58}")
 (iii,2011-01-09,{64})
(9 rows)
share|improve this answer
great answer. Thanks very much. – rorycl Aug 2 '11 at 21:55
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.