Обсуждение: How to return ARRAY from SQL function?

Hello,

in PostgreSQL 10.8 the following works -

words_ru=>         SELECT ARRAY[
words_ru->                 '*', '*', 'А', 'А', 'А', 'А', 'А', 'А', 'А', 'А',
words_ru->                 'Б', 'Б', 'В', 'В', 'В', 'В', 'Г', 'Г', 'Д', 'Д',
words_ru->                 'Д', 'Д', 'Е', 'Е', 'Е', 'Е', 'Е', 'Е', 'Е', 'Е',
words_ru->                 'Е', 'Ж', 'З', 'З', 'И', 'И', 'И', 'И', 'И', 'Й',
words_ru->                 'К', 'К', 'К', 'К', 'Л', 'Л', 'Л', 'Л', 'М', 'М',
words_ru->                 'М', 'Н', 'Н', 'Н', 'Н', 'Н', 'О', 'О', 'О', 'О',
words_ru->                 'О', 'О', 'О', 'О', 'О', 'О', 'П', 'П', 'П', 'П',
words_ru->                 'Р', 'Р', 'Р', 'Р', 'Р', 'С', 'С', 'С', 'С', 'С',
words_ru->                 'Т', 'Т', 'Т', 'Т', 'Т', 'У', 'У', 'У', 'У', 'Ф',
words_ru->                 'Х', 'Ц', 'Ч', 'Ш', 'Щ', 'Ъ', 'Ы', 'Ы', 'Ь', 'Ь',
words_ru->                 'Э', 'Ю', 'Я', 'Я'
words_ru->         ];
                                                                                                       array
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------
 {*,*,А,А,А,А,А,А,А,А,Б,Б,В,В,В,В,Г,Г,Д,Д,Д,Д,Е,Е,Е,Е,Е,Е,Е,Е,Е,Ж,З,З,И,И,И,И,И,Й,К,К,К,К,Л,Л,Л,Л,М,М,М,Н,Н,Н,Н,Н,О,О,О,О,О,О,О,О,О,О,П,П,П,П,Р,Р,Р,Р,Р,С,С,С,С,С,Т,Т,Т,Т,Т,У,У,У,У,Ф,Х,Ц,Ч,Ш,Щ,
Ъ,Ы,Ы,Ь,Ь,Э,Ю,Я,Я}
(1 row)

But creating an SQL function fails -

words_ru=> CREATE OR REPLACE FUNCTION words_all_letters()
words_ru->         RETURNS array AS
words_ru-> $func$
words_ru$>         SELECT ARRAY[
words_ru$>                 '*', '*', 'А', 'А', 'А', 'А', 'А', 'А', 'А', 'А',
words_ru$>                 'Б', 'Б', 'В', 'В', 'В', 'В', 'Г', 'Г', 'Д', 'Д',
words_ru$>                 'Д', 'Д', 'Е', 'Е', 'Е', 'Е', 'Е', 'Е', 'Е', 'Е',
words_ru$>                 'Е', 'Ж', 'З', 'З', 'И', 'И', 'И', 'И', 'И', 'Й',
words_ru$>                 'К', 'К', 'К', 'К', 'Л', 'Л', 'Л', 'Л', 'М', 'М',
words_ru$>                 'М', 'Н', 'Н', 'Н', 'Н', 'Н', 'О', 'О', 'О', 'О',
words_ru$>                 'О', 'О', 'О', 'О', 'О', 'О', 'П', 'П', 'П', 'П',
words_ru$>                 'Р', 'Р', 'Р', 'Р', 'Р', 'С', 'С', 'С', 'С', 'С',
words_ru$>                 'Т', 'Т', 'Т', 'Т', 'Т', 'У', 'У', 'У', 'У', 'Ф',
words_ru$>                 'Х', 'Ц', 'Ч', 'Ш', 'Щ', 'Ъ', 'Ы', 'Ы', 'Ь', 'Ь',
words_ru$>                 'Э', 'Ю', 'Я', 'Я'
words_ru$>         ];
words_ru$> $func$ LANGUAGE sql IMMUTABLE;
ERROR:  42601: syntax error at or near "array"
LINE 2:         RETURNS array AS
                        ^
LOCATION:  scanner_yyerror, scan.l:1128

Is it possible to create and return an array in an SQL custom function?

Thank you
Alex

Thank you, Laurenz and Tom -

On Fri, Jun 14, 2019 at 3:25 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Laurenz Albe <laurenz.albe@cybertec.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