Re: [GENERAL] Custom shuffle function stopped working in 9.6 - Mailing list pgsql-general

On Sat, Feb 11, 2017 at 5:37 PM, Alexander Farber
<alexander.farber@gmail.com> wrote:
...
> after switching to 9.6.2 from 9.5.3 the following custom function has
> stopped working:
> CREATE OR REPLACE FUNCTION words_shuffle(in_array varchar[])
>         RETURNS varchar[] AS
> Any suggestions for a better shuffling function please?

I've seen several sugestions and hints, but seem no one sugested the
classical shuffling algorithm. Even when of the solutions seems to be
not guaranteed to stop.

An easy way to shuffle is swap every element with a random one from
its position to the start or end ( NOT a random one on the array, this
will give you N^N combinations on an N element array, which does not
evenly divide the N! permutations on an array ( see at end ) ) ( of
course even my version is not going to give you that given random() is
not perfect, but it will be a bit better ).

Not having access to a server I've just tried this on 9.3 on sqlfiddlle:

CREATE OR REPLACE FUNCTION words_shuffle(in_array varchar[])
        RETURNS varchar[] AS
$$
declare
   a varchar[]:=in_array;
   n integer:=array_length(a,1);
   tmp varchar;
   r integer;
 begin
    for i in reverse n..2 loop
      r := floor(random()*i) + 1;
      tmp=a[i]; a[i]=a[r]; a[r]=tmp;
    end loop;
    return a;
 end
$$
LANGUAGE plpgsql volatile

As you can see I do it from the end swapping it with elements from the
start ( this way I swap i in the range 1..i, instead of i, n wich is a
little harder to debug ). I stop at 2 because element 1 can only be
swapped with itself. I've marked it volatile as it returns different
things each time you call it. My tests show it working, but it may
have some problems with the type conversions, as I'm not used to do
this kind of code in plpgsql, but you can get the idea.

Francisco Olarte.

P.S.:
-- shufflings of three elements, with any or from its pos to the end:


Swapping with any element in the array
0,0,0: abc =>swap(0,0)=> abc =>swap(1,0)=> bac =>swap(2,0)=> cab
0,0,1: abc =>swap(0,0)=> abc =>swap(1,0)=> bac =>swap(2,1)=> bca
0,0,2: abc =>swap(0,0)=> abc =>swap(1,0)=> bac =>swap(2,2)=> bac
0,1,0: abc =>swap(0,0)=> abc =>swap(1,1)=> abc =>swap(2,0)=> cba
0,1,1: abc =>swap(0,0)=> abc =>swap(1,1)=> abc =>swap(2,1)=> acb
0,1,2: abc =>swap(0,0)=> abc =>swap(1,1)=> abc =>swap(2,2)=> abc
0,2,0: abc =>swap(0,0)=> abc =>swap(1,2)=> acb =>swap(2,0)=> bca
0,2,1: abc =>swap(0,0)=> abc =>swap(1,2)=> acb =>swap(2,1)=> abc
0,2,2: abc =>swap(0,0)=> abc =>swap(1,2)=> acb =>swap(2,2)=> acb
1,0,0: abc =>swap(0,1)=> bac =>swap(1,0)=> abc =>swap(2,0)=> cba
1,0,1: abc =>swap(0,1)=> bac =>swap(1,0)=> abc =>swap(2,1)=> acb
1,0,2: abc =>swap(0,1)=> bac =>swap(1,0)=> abc =>swap(2,2)=> abc
1,1,0: abc =>swap(0,1)=> bac =>swap(1,1)=> bac =>swap(2,0)=> cab
1,1,1: abc =>swap(0,1)=> bac =>swap(1,1)=> bac =>swap(2,1)=> bca
1,1,2: abc =>swap(0,1)=> bac =>swap(1,1)=> bac =>swap(2,2)=> bac
1,2,0: abc =>swap(0,1)=> bac =>swap(1,2)=> bca =>swap(2,0)=> acb
1,2,1: abc =>swap(0,1)=> bac =>swap(1,2)=> bca =>swap(2,1)=> bac
1,2,2: abc =>swap(0,1)=> bac =>swap(1,2)=> bca =>swap(2,2)=> bca
2,0,0: abc =>swap(0,2)=> cba =>swap(1,0)=> bca =>swap(2,0)=> acb
2,0,1: abc =>swap(0,2)=> cba =>swap(1,0)=> bca =>swap(2,1)=> bac
2,0,2: abc =>swap(0,2)=> cba =>swap(1,0)=> bca =>swap(2,2)=> bca
2,1,0: abc =>swap(0,2)=> cba =>swap(1,1)=> cba =>swap(2,0)=> abc
2,1,1: abc =>swap(0,2)=> cba =>swap(1,1)=> cba =>swap(2,1)=> cab
2,1,2: abc =>swap(0,2)=> cba =>swap(1,1)=> cba =>swap(2,2)=> cba
2,2,0: abc =>swap(0,2)=> cba =>swap(1,2)=> cab =>swap(2,0)=> bac
2,2,1: abc =>swap(0,2)=> cba =>swap(1,2)=> cab =>swap(2,1)=> cba
2,2,2: abc =>swap(0,2)=> cba =>swap(1,2)=> cab =>swap(2,2)=> cab
F(abc) = 4
F(acb) = 5
F(bac) = 5
F(bca) = 5
F(cab) = 4
F(cba) = 4
Swapping from its own position in the array to the end ( last can be
omitted, of course )
0,1,2: abc =>swap(0,0)=> abc =>swap(1,1)=> abc =>swap(2,2)=> abc
0,2,2: abc =>swap(0,0)=> abc =>swap(1,2)=> acb =>swap(2,2)=> acb
1,1,2: abc =>swap(0,1)=> bac =>swap(1,1)=> bac =>swap(2,2)=> bac
1,2,2: abc =>swap(0,1)=> bac =>swap(1,2)=> bca =>swap(2,2)=> bca
2,1,2: abc =>swap(0,2)=> cba =>swap(1,1)=> cba =>swap(2,2)=> cba
2,2,2: abc =>swap(0,2)=> cba =>swap(1,2)=> cab =>swap(2,2)=> cab
F(abc) = 1
F(acb) = 1
F(bac) = 1
F(bca) = 1
F(cab) = 1
F(cba) = 1