Re: How to return ARRAY from SQL function?

Thank you, Laurenz and Tom -

On Fri, Jun 14, 2019 at 3:25 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> writes:
>
> > You'll have to specify an array of which type you want, probably
> > ... RETURNS text[]
>
> Right. Also, I don't recall the exact rules in this area, but I think
> that SQL functions are pickier about their return types than ordinary
> query contexts, meaning you might also need an explicit cast:
>
> SELECT ARRAY[
> '*', '*', 'А', 'А', 'А', 'А', 'А', 'А', 'А', 'А',
> ...
> ]::text[];
>
> Try it without first, but if it moans about the query returning the
> wrong type, that's how to fix it.
>
>

this has worked for me:

CREATE OR REPLACE FUNCTION words_all_letters()
RETURNS text[] AS
$func$
SELECT ARRAY[
'*', '*',
'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A',
'B', 'B',
'C', 'C',
'D', 'D', 'D', 'D',
'E', 'E', 'E', 'E', 'E', 'E', 'E', 'E', 'E', 'E', 'E', 'E',
'F', 'F',
'G', 'G', 'G',
'H', 'H',
'I', 'I', 'I', 'I', 'I', 'I', 'I', 'I', 'I',
'J',
'K',
'L', 'L', 'L', 'L',
'M', 'M',
'N', 'N', 'N', 'N', 'N', 'N',
'O', 'O', 'O', 'O', 'O', 'O', 'O', 'O',
'P', 'P',
'Q',
'R', 'R', 'R', 'R', 'R', 'R',
'S', 'S', 'S', 'S',
'T', 'T', 'T', 'T', 'T', 'T',
'U', 'U', 'U', 'U',
'V', 'V',
'W', 'W',
'X',
'Y', 'Y',
'Z'
];
$func$ LANGUAGE sql IMMUTABLE;

And then I shuffle the letters by -

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

Regards
Alex