Re: How to return ARRAY from SQL function? - Mailing list pgsql-general

From Alexander Farber
Subject Re: How to return ARRAY from SQL function?
Date
Msg-id CAADeyWjshf=VGsk0CcbFgQ-196QLrgFjrsANdA8T+2+ha4HUww@mail.gmail.com
Whole thread Raw
In response to Re: How to return ARRAY from SQL function?  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: How to return ARRAY from SQL function?
List pgsql-general
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

pgsql-general by date:

Previous
From: "Peter J. Holzer"
Date:
Subject: Re: Copy Bulk Ignore Duplicated
Next
From: Tom Lane
Date:
Subject: Re: How to return ARRAY from SQL function?