PostgreSQL将函数返回的数组转换为列 [英] PostgreSQL convert array returned from function to columns

问题描述

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

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):

{1, 2}

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

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

[ 1 | 2 ]

我发现我可以做到：

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

这是我所拥有的东西的几乎完美的副本：

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.

推荐答案

可以使用子选择吗？

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

这仍然需要您明确提取每一列（就像您已经做）。如果数组中的元素多于提取的元素，则它们将丢失；如果元素少，则丢失的列将仅为 NULL 。

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.

编辑：我想我会将整个内容包装在一个subselect中；内部子选择生成所需的行，外部选择将内部查询投射到所需的列：

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;

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

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

这篇关于PostgreSQL将函数返回的数组转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！