postgresql 如何将函数应用于 Postgres 中数组列的每个元素？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8584119/
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
How to apply a function to each element of an array column in Postgres?
提问by punkish
A Pg query returns an array. I would like to retrieve that with each element formatted to 3 decimal places. How can I apply a function to each element of an array? Something like the following (wrong, obviously) --
Pg 查询返回一个数组。我想用每个元素格式化为 3 个小数位来检索它。如何将函数应用于数组的每个元素？类似于以下内容（显然是错误的）-
SELECT Round(ARRAY[1.53224,0.23411234], 2);
{1.532, 0.234}
I guess I am looking for something like Perl's mapfunction.
我想我正在寻找类似 Perlmap函数的东西。
采纳答案by mask8
You may need to create a stored function. Here is the one that does what you need:
您可能需要创建一个存储函数。这是一个可以满足您需求的方法：
CREATE OR REPLACE FUNCTION array_round(float[], int)
RETURNS float[]
AS
$$
DECLARE
arrFloats ALIAS FOR ;
roundParam ALIAS FOR ;
retVal float[];
BEGIN
FOR I IN array_lower(arrFloats, 1)..array_upper(arrFloats, 1) LOOP
retVal[I] := round(CAST(arrFloats[I] as numeric), roundParam);
END LOOP;
RETURN retVal;
END;
$$
LANGUAGE plpgsql
STABLE
RETURNS NULL ON NULL INPUT;
Then call something like this:
然后像这样调用：
# SELECT array_round(ARRAY[1.53224,0.23411234], 2);
array_round
-------------
{1.53,0.23}
(1 row)
回答by Joey Adams
First, turn the array into a set using unnest:
首先，使用unnest将数组转换为集合：
> SELECT n FROM unnest(ARRAY[1.53224,0.23411234]) AS n;
n
------------
1.53224
0.23411234
(2 rows)
Then, apply an expression to the column:
然后，将表达式应用于该列：
> SELECT ROUND(n, 2) FROM unnest(ARRAY[1.53224,0.23411234]) AS n;
round
-------
1.53
0.23
(2 rows)
Finally, use array_aggto turn the set back into an array:
最后，使用array_agg将集合转回数组：
> SELECT array_agg(ROUND(n, 2)) FROM unnest(ARRAY[1.53224,0.23411234]) AS n;
array_agg
-------------
{1.53,0.23}
(1 row)
回答by Pavel Stehule
postgres=# select array(select round(unnest(array[1.2,2.4,3,4])));
array
-----------
{1,2,3,4}
(1 row)
回答by Edmund
You need to turn the array into a row set. For example, using generate_series:
您需要将数组转换为行集。例如，使用generate_series：
SELECT ARRAY(SELECT ROUND(ARRAY[1.53224,0.23411234])[i], 2) FROM generate_series(1,2) AS s(i));
I know that's pretty ugly. There should be a helper function to make such mappings easier.
我知道这很丑陋。应该有一个辅助函数来使这种映射更容易。
Perhaps something like (yes it's horrible, slow, and brittle dynamic code):
也许是这样的（是的，它是可怕的、缓慢的、脆弱的动态代码）：
CREATE OR REPLACE FUNCTION map_with_arg(TEXT, ANYARRAY, TEXT)
RETURNS ANYARRAY
IMMUTABLE STRICT
LANGUAGE 'plpgsql' AS
$$
DECLARE
i INTEGER;
t TEXT;
cmd TEXT;
BEGIN
FOR i IN array_lower(, 1) .. array_upper(, 1) LOOP
cmd := 'SELECT ('||quote_ident()||'('||quote_nullable([i])||', '||quote_nullable()||'))::TEXT';
EXECUTE cmd INTO t;
[i] := t;
END LOOP;
RETURN ;
END;
$$;
select map_with_arg('repeat', array['can','to']::TEXT[], '2');
map_with_arg
---------------
{cancan,toto}
UpdateIt occurs to me that we could use a single dynamic statement for the whole loop. This could mitigate some of the performance concerns.
更新我突然想到我们可以对整个循环使用单个动态语句。这可以减轻一些性能问题。
CREATE OR REPLACE FUNCTION map_with_arg(TEXT, ANYARRAY, TEXT)
RETURNS ANYARRAY
IMMUTABLE STRICT
LANGUAGE 'plpgsql' AS
$$
DECLARE
cmd TEXT;
rv TEXT;
BEGIN
cmd := 'SELECT ARRAY(SELECT (' || quote_ident()||'([i], '||quote_nullable()||'))::TEXT FROM generate_subscripts(, 1) AS gs(i))';
EXECUTE cmd USING INTO rv;
RETURN rv;
END;
$$;
