Обсуждение: Problem with selecting arrays in set-returning plpgsql function

I am trying to select arrays in a set-returning function, 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. The return type into which
locationnodes.rs_people_c is returned is INTEGER[].

Assistance much appreciated.
Rory

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
            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[]
    );

data:
    select rs_people_c from locationnodes;
                           rs_people_c
    ---------------------------------------------
     {}
     {}
     {}
     {40}
     {28}
     {}
     {1}
     {}
     {36}
     {731}
     {32}
     {31}
     {66}
     {}
     {}
     {}
     {62}
     {540,72,69,53,37,42,201,65,560,51,58}
     {64}

On 02/08/11, Rory Campbell-Lange (rory@campbell-lange.net) wrote:
> I am trying to select arrays in a set-returning function, 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. The return type into which
> locationnodes.rs_people_c is returned is INTEGER[].

My mistake was due to creating another return type expecting an array,
which received a simple INT.

Now sorted.
Rory