Postgresql：在设置返回 plpgsql 函数中选择数组时出现问题

Question

我试图在 postgres 8.4 中的集合返回函数中选择数组，但是接收 错误：

“数组值必须以“{”或维度信息开头”。

此问题有时似乎与 locationnodes.rs_people_c 有关 有一个空数组。我试图用 COALESCE 语句来解决这个问题。运气不好。

函数：

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;

类型：

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

数据

select rs_people_c from locationnodes;
           rs_people_c
-------------------------------------
 {}
 {}
 {731}
 {32}
 {31}
 {}
 {62}
 {540,72,69,53,37,42,201,51,58}
 {64}

Answer 1

我犯了一个愚蠢的类型定义错误（我从最初的问题中排除了该错误，但 Grzegorz 提到了——谢谢 Grzegorz）。

我将此发布作为对任何在 google 上搜索数组值必须以“{”或维度信息开头的问题的人的答案。

就我而言，问题在于位置返回类型被定义为整数数组，但该函数返回一个简单的整数。不幸的是，Postgres 没有提供有关这些情况下具体问题的更多信息。

另请注意，在这种情况下，您不需要像我在问题中所做的那样将 NULL 数组合并到 {} 。

简而言之：确保您的数组返回类型正在接收数组输入！

Answer 2

除了 @rorycl 的答案之外，这里还在 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 函数：

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;

结果：

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)