Problem with selecting arrays in set-returning plpgsql function

From: Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Problem with selecting arrays in set-returning plpgsql function
Date: 2011-08-02 19:05:48
Message-ID: 20110802190548.GA6327@campbell-lange.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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}

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pedro Sam 2011-08-02 19:17:58 Re: Timeline Conflict
Previous Message devrim 2011-08-02 18:12:15 Re: 9.0 Streaming Replication Problem to two slaves