如何在PostgreSQL 9.6及更低版本中改组数组?

本文介绍了如何在PostgreSQL 9.6及更低版本中改组数组?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

以下自定义存储功能-

CREATE OR REPLACE FUNCTION words_shuffle(in_array varchar[]) RETURNS varchar[] AS $func$ SELECT array_agg(letters.x) FROM (SELECT UNNEST(in_array) x ORDER BY RANDOM()) letters; $func$ LANGUAGE sql STABLE;

在PostgreSQL 9.5.3中是改组字符数组:

words=> select words_shuffle(ARRAY['a','b','c','d','e','f']); words_shuffle --------------- {c,d,b,a,e,f} (1 row)

但是现在我切换到PostgreSQL 9.6.2之后，该函数停止工作了:

words=> select words_shuffle(ARRAY['a','b','c','d','e','f']); words_shuffle --------------- {a,b,c,d,e,f} (1 row)

可能是因为ORDER BY RANDOM()停止了工作:

words=> select unnest(ARRAY['a','b','c','d','e','f']) order by random(); unnest -------- a b c d e f (6 rows)

我正在寻找一种更好的方法来改组字符数组，该方法在新的PostgreSQL 9.6和9.5中都可以使用.

我的开发中使用Pl/PgSQL函数的我的文字游戏需要它.

更新:

通过汤姆巷:现在在ORDER BY之后扩展目标列表中的SRF. 因此，ORDER BY正在对单个哑行进行排序，然后对非嵌套行进行排序 之后发生.见

git.postgresql. org/gitweb/?p = postgresql.git& a = commitdiff& h = 9118d03a8

解决方案

通常，应在FROM子句中放置一个返回函数集:

select array_agg(u order by random()) from unnest(array['a','b','c','d','e','f']) u array_agg --------------- {d,f,b,e,c,a} (1 row)

对于文档(强调):

当前，返回集合的函数也可以在查询的选择列表中调用.对于查询本身生成的每一行，将调用函数返回集，并为函数结果集的每个元素生成一个输出行.但是请注意，此功能已被弃用，并且在以后的版本中可能会删除.

The following custom stored function -

CREATE OR REPLACE FUNCTION words_shuffle(in_array varchar[]) RETURNS varchar[] AS $func$ SELECT array_agg(letters.x) FROM (SELECT UNNEST(in_array) x ORDER BY RANDOM()) letters; $func$ LANGUAGE sql STABLE;

was shuffling character array in PostgreSQL 9.5.3:

words=> select words_shuffle(ARRAY['a','b','c','d','e','f']); words_shuffle --------------- {c,d,b,a,e,f} (1 row)

But now after I have switched to PostgreSQL 9.6.2 the function stopped working:

words=> select words_shuffle(ARRAY['a','b','c','d','e','f']); words_shuffle --------------- {a,b,c,d,e,f} (1 row)

Probably because the ORDER BY RANDOM() stopped working:

words=> select unnest(ARRAY['a','b','c','d','e','f']) order by random(); unnest -------- a b c d e f (6 rows)

I am looking please for a better method to shuffle character array, which would work in the new PostgreSQL 9.6, but also in 9.5.

I need it for my word game in development, which uses Pl/PgSQL functions.

UPDATE:

Reply by Tom Lane:

Expansion of SRFs in the targetlist now happens after ORDER BY. So the ORDER BY is sorting a single dummy row and then the unnest happens after that. See

git.postgresql/gitweb/?p=postgresql.git&a=commitdiff&h=9118d03a8

解决方案

Generally, a set returning function should be placed in FROM clause:

select array_agg(u order by random()) from unnest(array['a','b','c','d','e','f']) u array_agg --------------- {d,f,b,e,c,a} (1 row)

For the documentation (emphasis added):

Currently, functions returning sets can also be called in the select list of a query. For each row that the query generates by itself, the function returning set is invoked, and an output row is generated for each element of the function's result set. Note, however, that this capability is deprecated and might be removed in future releases.