postgresql PostgreSQL将从函数返回的数组转换为列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6960247/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL convert array returned from function to columns
提问by lightningmanic
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
更新
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.
同样，这会从数组中生成两列，但我的函数被调用了两次，这使我的运行时间加倍。
采纳答案by SingleNegationElimination
can you use a 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 table1references.
内部和外部选择将正确关联table1参考。
回答by Saurabh
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
回答by mu is too short
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:
最接近的事情是unnest：
expand an array to a set of rows
将数组扩展为一组行
but that gives you rows rather the columns you want.
但这会给你行而不是你想要的列。
