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}
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 |