How to shuffle array in PostgreSQL 9.6 and also lower versions?

Question

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

klin · Accepted Answer · 2017-02-11 17:53:53Z

10

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.

answered Feb 11, 2017 at 17:53

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gordon Linoff Over a year ago

The difference between the from clause and the select really has nothing to do with why this would have stopped working. Although the answer is correct and the sentiment correct (put row generating functions in the from clause), the reasoning is off.

klin Over a year ago

I've never had a problem with that as I never use SRFs in SELECT clause. I think one should adopt the rule, as the case described in the question is not a single known problem caused by misuse of SRFs.

Gordon Linoff · Accepted Answer · 2017-02-11 18:41:39Z

No doubt, this is a change and due to some "improvement" in the optimizer. Given that the documentation sort of says that this works, it is frustrating.

However, I would suggest that you not depend on the subquery:

SELECT array_agg(letters.x ORDER BY random())
FROM UNNEST(in_array) l(x);

This should also work in order versions of Postgres.

The documentation says:

Alternatively, supplying the input values from a sorted subquery will usually work. For example:
SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
But this syntax is not allowed in the SQL standard, and is not portable to other database systems.

(I freely admit that "will usually work" is not a guarantee. But having a substandard code sample in the documentation is really misleading. Why doesn't it show the correct sample using the ORDER BY clause in the aggregation function?)

https://www.postgresql.org/docs/9.5/static/functions-aggregate.html

Collectives™ on Stack Overflow

How to shuffle array in PostgreSQL 9.6 and also lower versions?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related