Plpgsql-选择多列的数组 [英] plpgsql - selecting array of multiple columns

问题描述

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

这行得通：

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有类似的事情吗？与记录类似，但用于数组。

推荐答案

您将在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中的关键字，因为它是一种数据类型。不应将其用作列名

这篇关于Plpgsql-选择多列的数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！