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
https://git.postgresql.org/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.