Thread: 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
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
Alexander Farber wrote: > 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[... "array" is not an existing data type. You'll have to specify an array of which type you want, probably ... RETURNS text[] Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Laurenz Albe <laurenz.albe@cybertec.at> writes: > Alexander Farber wrote: >> 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[... > "array" is not an existing data type. > 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. regards, tom lane
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
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
Alexander Farber <alexander.farber@gmail.com> writes: > 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; Hmm ... that's not really "stable", since it depends on random() which is volatile. regards, tom lane
Thank you -
On Tue, Jun 18, 2019 at 3:10 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alexander Farber <alexander.farber@gmail.com> writes:
> 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;
Hmm ... that's not really "stable", since it depends on random()
which is volatile.
I was wondering that too, but assumed it is maybe STABLE because the function does not modify any tables.
I will remove the STABLE keyword now
Regards
Alex