Postgresql: Problem with selecting arrays in set-returning plpgsql function

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:

CR开发者_运维百科EATE 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}

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!

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)