PostgreSQL转换数组从函数返回到列

I have a function in PostgreSQL (PLPGSQL) that returns an array containing two elements. When I run a select statement calling the function, I get a column containing the array (as expected):

我在PostgreSQL（PLPGSQL）中有一个函数，它返回一个包含两个元素的数组。当我运行一个调用该函数的select语句时，我得到一个包含该数组的列（如预期的那样）：

{1, 2}

What I really would like to do is extract these elements to be their own columns:

我真正想要做的是将这些元素提取为自己的列：

[ 1 | 2 ]

I have found that I can do:

我发现我能做到：

SELECT (MyFunction())[1], (MyFunction())[2]

But that calls the function twice, therefore doubling the run time (this function is a very time-consuming function). Is there a better way to handle this?

但是它会调用该函数两次，因此运行时间加倍（此函数是一个非常耗时的函数）。有没有更好的方法来处理这个？

UPDATE

UPDATE

Here is an almost perfect replica of what I have:

这是我所拥有的几乎完美的复制品：

SELECT table1.a, table1.b, table1.c, (MyFunction(table1.a, table1.b, table1.c))[1],
(MyFunction(table1.a, table1.b, table1.c))[2]
FROM table1
INNER JOIN table2 using(b)
WHERE ... GROUP BY table1.a, table1.b, table1.c;

Again, this produces the two columns from the array, but my function is called twice, which doubles my run time.

同样，这会产生数组中的两列，但我的函数被调用两次，这使我的运行时间加倍。

3 个解决方案

#1

can you use a subselect?

你能用subselect吗？

postgres=# select ar[1], ar[2] from (select string_to_array('a b c', ' ') ar) as sq;
 ar | ar 
----+----
 a  | b
(1 row)

This still requires you explicitly extract each column (like you already do). If there are more elements in the array than extracted, they will be lost, and if there are fewer, then the missing columns will just be NULL.

这仍然需要您显式提取每列（就像您已经做过的那样）。如果数组中的元素多于提取的元素，则它们将丢失，如果元素数量较少，则缺少的列将仅为NULL。

EDIT: I think I would wrap the whole thing in a subselect; the inner subselect generates the desired rows, with the outer select projecting the inner query into the desired columns:

编辑：我想我会把整个东西包裹在一个子选择中;内部子选择生成所需的行，外部选择将内部查询投影到所需的列中：

SELECT subquery1.a, subquery1.b, subquery1.c, 
    myfunction_result[1], myfunction_result[2] 
FROM ( SELECT table1.a, table1.b, table1.c,
              MyFunction(table1.a, table1.b, table1.c) as myfunction_result
       FROM table1 INNER JOIN table2 using(b) 
       WHERE ... GROUP BY table1.a, table1.b, table1.c
) AS subquery1;

The inner and outer selects will properly correlate the table1 references.

内部和外部选择将正确关联table1引用。

#2

You can't do that. A single array column could have, for example, one array with three elements and another with five elements. If you tried to expand those arrays into individual columns, you'd end up with two rows in a result set that have different numbers of columns and that is not allowed.

你不能这样做。例如，单个阵列列可以具有一个具有三个元素的阵列，另一个具有五个元素。如果您尝试将这些数组扩展为单独的列，那么结果集中的两行最终会有不同的列数，并且不允许这样做。

The closest thing available is unnest:

最接近的东西是不需要的：

expand an array to a set of rows

将数组扩展为一组行

but that gives you rows rather the columns you want.

但是这会给你行而不是你想要的列。

#3

select data[1] as id, data[2] as value from (SELECT 
string_to_array(rs,':') as data from unnest(string_to_array('1:234,2:400',',')) as rs) as foo

This will result as:

这将导致：

id|Value
--------
1 | 234
2 | 400

#1