Re: proposal: jsonb_populate_array

po 14. 8. 2023 v 15:47 odesílatel Chapman Flack <chap(at)anastigmatix(dot)net>
napsal:

> On 2023-08-14 09:11, Erik Rijkers wrote:
> > , '$' returning date[]
>
> I certainly like that syntax better.
>
> It's not that the "here's a null to tell you the type I want"
> is terribly unclear, but it seems not to be an idiom I have
> seen a lot of in PostgreSQL before now. Are there other places
> it's currently used that I've overlooked?
>

It is used only for hstore, json, jsonb function if I remember correctly.

I dislike this idiom too, but SQL cannot use type as parameter. I proposed
anytype polymorphic pseudotype so instead

fx(null::int, ...) you can write (theoretically) fx('int', ...), but it
doesn't look too much better. For composite functions we can dynamically to
specify structure as SELECT FROM fx(...) AS (a int, b int), but it cannot
be used for scalar functions and cannot be used for static composite types.

I can imagine some special syntax of CAST, that can push type to inside
function, and allows to us to write functions like fx(non polymorphic
types) RETURNS any

for proposed functionality it can look like SELECT
CAST(json_populate_array('[]'::jsonb) AS date[])

> Regards,
> -Chap
>