postgresql - plpgsql - 选择多列数组

Question

我正在尝试创建选择数据、处理并返回它们的过程，但我正在努力如何为多列定义数组变量。

这个有效:

CREATE OR REPLACE FUNCTION testing_array_return()
RETURNS TABLE(id BIGINT) AS
$body$
DECLARE
    l_rows BIGINT[];
BEGIN
    -- select data using for update etc
        l_rows := ARRAY(
        SELECT 1 AS id
        UNION 
        SELECT 2 AS id
    );
    -- do some stuff
    -- return previously selected data
    RETURN QUERY 
        SELECT *
            FROM UNNEST(l_rows);
END;
$body$
LANGUAGE 'plpgsql'

但我想在不使用复合类型或行类型的情况下对 2 列或更多列执行此操作:

CREATE OR REPLACE FUNCTION testing_array_return()
RETURNS TABLE(id BIGINT, text VARCHAR2) AS
$body$
DECLARE
    l_rows -- what should I put here?
BEGIN
    -- select data using for update etc
        l_rows := ARRAY(
        SELECT 1 AS id, 'test' AS text
        UNION 
        SELECT 2 AS id, 'test2' AS text
    );
    -- do some stuff
    -- return previously selected data
    RETURN QUERY 
        SELECT *
            FROM UNNEST(l_rows);
END;
$body$
LANGUAGE 'plpgsql'

在 oracle 中，我可以定义记录类型，然后定义它的表类型，但我找不到如何在 postgres 中执行此操作。搜索时可能使用了错误的关键字...

编辑:这就是我在 Oracle 中执行此操作的方式(不返回)。

DECLARE
  TYPE t_row IS RECORD(
     id   NUMBER
    ,text VARCHAR2(10));
  TYPE t_tbl IS TABLE OF t_row;
  l_rows t_tbl := t_tbl(); --how to do this in postgres?
BEGIN
  SELECT *
    BULK COLLECT
    INTO l_rows
    FROM (SELECT 1 AS id
                ,'test' AS text
            FROM dual
          UNION ALL
          SELECT 2 AS id
                ,'test' AS text
            FROM dual);
END;

postgres 中有类似的东西吗？类似于记录，但用于数组。

Answer 1

你会在 Postgres 中做同样的事情:

创建记录类型:

create type footype as (id bigint, text_ varchar);

然后在您的函数中使用该类型的数组:

CREATE OR REPLACE FUNCTION testing_array_return()
RETURNS TABLE(id BIGINT, text VARCHAR) AS
$body$
DECLARE
    l_rows footype[];
BEGIN
    -- select data using for update etc
        l_rows := ARRAY(
        SELECT (1, 'test')
        UNION 
        SELECT (2, 'test2')
    );
    -- do some stuff
    -- return previously selected data
    RETURN QUERY 
        SELECT *
            FROM UNNEST(l_rows);
END;
$body$
LANGUAGE plpgsql;

在线示例:http://rextester.com/UMRZFO54266

表达式 (1, 'test') 创建记录类型的单个值。由于它已分配给类型化变量，因此无需为列设置别名(实际上您无论如何也不能这样做)。

无关，但是:语言名称是一个标识符。不要将其放在单引号中。

---

^{请注意 text 也是 Postgres 中的关键字，因为它是一种数据类型。您不应将其用作列名}