Re: Custom shuffle function stopped working in 9.6

[Melvin Davidson <melvin6925@xxxxxxxxx>]

I don't use an array, but perhaps you can adapt to this function which works in 9.6.1

CREATE OR REPLACE FUNCTION public.scramble(text)
  RETURNS text AS
$BODY$
DECLARE
    p_in    ALIAS FOR $1;
    v_out    TEXT DEFAULT '';
    v_mod    TEXT;
    v_len    INT4;
    v_ctr    INT4;
    v_pos    INT4;
    v_array CHAR[];

BEGIN

v_ctr = 1;
WHILE v_ctr <= LENGTH(p_in) LOOP
  v_array[v_ctr] = NULL;
  v_ctr := v_ctr +1;
END LOOP;

v_ctr = 1;
WHILE v_ctr <= LENGTH(p_in) LOOP

        v_pos := INT4(random() * 100);
        IF v_pos > LENGTH(p_in) OR v_array[v_pos] IS NOT NULL THEN
            CONTINUE;
        END IF;
       
    v_mod := substring(p_in from v_pos for 1);
       
    v_array[v_pos] := '*';
    v_out := v_out || v_mod;
    v_ctr := v_ctr +1;

END LOOP;

RETURN v_out;
END;
-- SAMPLE CALL
-- SELECT scramble('Honor');
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.scramble(text)
  OWNER TO postgres;

On Sat, Feb 11, 2017 at 12:17 PM, Alexander Farber <alexander.farber@xxxxxxxxx> wrote:

I think ORDER BY RANDOM() has stopped working in 9.6.2:

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

 unnest 

--------

 a

 b

 c

 d

 e

 f

(6 rows)

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.