proposal: jsonb_populate_array

On 2023-Aug-14, Pavel Stehule wrote:

jsonb_populate_array(anyarray, jsonb) returns anyarray

Usage:

select jsonb_populate_array(null::text[], '["cust_full_name","cust_email"]')

I don't understand what this does. Can you be more explicit?

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
Maybe there's lots of data loss but the records of data loss are also lost.
(Lincoln Yeoh)

po 14. 8. 2023 v 11:32 odesílatel Alvaro Herrera <alvherre@alvh.no-ip.org>
napsal:

On 2023-Aug-14, Pavel Stehule wrote:

jsonb_populate_array(anyarray, jsonb) returns anyarray

Usage:

select jsonb_populate_array(null::text[],

'["cust_full_name","cust_email"]')

I don't understand what this does. Can you be more explicit?

example

'["2023-07-13","2023-07-14"]'::jsonb --> {2023-07-13,2023-07-14}::date[]

Now, I have to transform to table, casting, and back transformation to
array, and I cannot to write generic function. I can run just "slow" query

select array_agg(value::date) from
jsonb_array_elements_text('["2023-07-13","2023-07-14"]'::jsonb);

with proposed function I can write

select jsonb_populate_array(null:date[],
'["2023-07-13","2023-07-14"]'::jsonb)

Regards

Pavel

Op 8/14/23 om 14:51 schreef Pavel Stehule:> po 14. 8. 2023 v 11:32
odesílatel Alvaro Herrera <alvherre@alvh.no-ip.org>

with proposed function I can write

select jsonb_populate_array(null:date[],
'["2023-07-13","2023-07-14"]'::jsonb)

Not yet committed, but outstanding
SQL/JSON patches (v11) will let you do:

select json_query(
'["2023-07-13", "2023-07-14"]'::jsonb
, '$' returning date[]
);
json_query
-------------------------
{2023-07-13,2023-07-14}
(1 row)

That's (more or less) what you want, no?

Let's hope it gets submitted 17-ish, anyway

Erik

po 14. 8. 2023 v 15:09 odesílatel Erik Rijkers <er@xs4all.nl> napsal:

Op 8/14/23 om 14:51 schreef Pavel Stehule:> po 14. 8. 2023 v 11:32
odesílatel Alvaro Herrera <alvherre@alvh.no-ip.org>

with proposed function I can write

select jsonb_populate_array(null:date[],
'["2023-07-13","2023-07-14"]'::jsonb)

Not yet committed, but outstanding
SQL/JSON patches (v11) will let you do:

select json_query(
'["2023-07-13", "2023-07-14"]'::jsonb
, '$' returning date[]
);
json_query
-------------------------
{2023-07-13,2023-07-14}
(1 row)

That's (more or less) what you want, no?

Yes, the functionality is exactly the same, but still maybe for completeness
the function json_populate_array can be nice.

In old API the transformations between json and row/record types is well
covered, but for array, only direction array->json is covered

I think so this can be +/- 40 lines of C code

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?

Regards,
-Chap

po 14. 8. 2023 v 15:47 odesílatel Chapman Flack <chap@anastigmatix.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[])

\df jsonb_populate_record
List of functions
Schema | Name | Result data type | Argument data
types | Type
------------+-----------------------+------------------+---------------------+------
pg_catalog | jsonb_populate_record | anyelement | anyelement,
jsonb | func
(1 row)

manual:

anyelement Indicates that a function accepts any data type.
For the “simple” family of polymorphic types, the matching and deduction rules work like this:
Each position (either argument or return value) declared as anyelement is allowed to have any specific actual data type, but in any given call they must all be the same actual type.

So jsonb_populate_record signature can handle cases like
jsonb_populate_record(anyarray, jsonb)? obviously this is a cast, it
may fail.
also if input is anyarray, so the output anyarray will have the same
base type as input anyarray.

On 8/14/23 15:47, Chapman Flack wrote:

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 has been used since forever in polymorphic aggregate final functions.
I don't mind it there, but I do not like it in general user-facing
functions.

https://www.postgresql.org/docs/current/sql-createaggregate.html
--
Vik Fearing

On 8/14/23 15:37, Pavel Stehule wrote:

po 14. 8. 2023 v 15:09 odesílatel Erik Rijkers <er@xs4all.nl> napsal:

Op 8/14/23 om 14:51 schreef Pavel Stehule:> po 14. 8. 2023 v 11:32
odesílatel Alvaro Herrera <alvherre@alvh.no-ip.org>

with proposed function I can write

select jsonb_populate_array(null:date[],
'["2023-07-13","2023-07-14"]'::jsonb)

Not yet committed, but outstanding
SQL/JSON patches (v11) will let you do:

select json_query(
'["2023-07-13", "2023-07-14"]'::jsonb
, '$' returning date[]
);
json_query
-------------------------
{2023-07-13,2023-07-14}
(1 row)

That's (more or less) what you want, no?

Yes, the functionality is exactly the same, but still maybe for completeness
the function json_populate_array can be nice.

In old API the transformations between json and row/record types is well
covered, but for array, only direction array->json is covered

I don't think we should be extending the old API when there are Standard
ways of doing the same thing. In fact, I would like to see the old way
slowly be deprecated.

I think so this can be +/- 40 lines of C code

It seems to me like a good candidate for an extension.
--
Vik Fearing

út 15. 8. 2023 v 7:48 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

On 8/14/23 15:37, Pavel Stehule wrote:

po 14. 8. 2023 v 15:09 odesílatel Erik Rijkers <er@xs4all.nl> napsal:

Op 8/14/23 om 14:51 schreef Pavel Stehule:> po 14. 8. 2023 v 11:32
odesílatel Alvaro Herrera <alvherre@alvh.no-ip.org>

with proposed function I can write

select jsonb_populate_array(null:date[],
'["2023-07-13","2023-07-14"]'::jsonb)

Not yet committed, but outstanding
SQL/JSON patches (v11) will let you do:

select json_query(
'["2023-07-13", "2023-07-14"]'::jsonb
, '$' returning date[]
);
json_query
-------------------------
{2023-07-13,2023-07-14}
(1 row)

That's (more or less) what you want, no?

Yes, the functionality is exactly the same, but still maybe for

completeness

the function json_populate_array can be nice.

In old API the transformations between json and row/record types is well
covered, but for array, only direction array->json is covered

I don't think we should be extending the old API when there are Standard
ways of doing the same thing. In fact, I would like to see the old way
slowly be deprecated.

I think so this can be +/- 40 lines of C code

It seems to me like a good candidate for an extension.

Unfortunately, these small extensions have zero chance to be available for
users that use some cloud postgres.

út 15. 8. 2023 v 5:12 odesílatel jian he <jian.universality@gmail.com>
napsal:

\df jsonb_populate_record
List of functions
Schema | Name | Result data type | Argument data
types | Type

------------+-----------------------+------------------+---------------------+------
pg_catalog | jsonb_populate_record | anyelement | anyelement,
jsonb | func
(1 row)

manual:

anyelement Indicates that a function accepts any data type.
For the “simple” family of polymorphic types, the matching and deduction

rules work like this:

Each position (either argument or return value) declared as anyelement

is allowed to have any specific actual data type, but in any given call
they must all be the same actual type.

So jsonb_populate_record signature can handle cases like
jsonb_populate_record(anyarray, jsonb)? obviously this is a cast, it
may fail.
also if input is anyarray, so the output anyarray will have the same
base type as input anyarray.

It fails (what is expected - else be too strange to use function in name
"record" for arrays)

(2023-08-15 07:57:40) postgres=# select
jsonb_populate_record(null::varchar[], '[1,2,3]');
ERROR: first argument of jsonb_populate_record must be a row type

regards

Pavel

On 8/15/23 07:53, Pavel Stehule wrote:

út 15. 8. 2023 v 7:48 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

On 8/14/23 15:37, Pavel Stehule wrote:

po 14. 8. 2023 v 15:09 odesílatel Erik Rijkers <er@xs4all.nl> napsal:

I think so this can be +/- 40 lines of C code

It seems to me like a good candidate for an extension.

Unfortunately, these small extensions have zero chance to be available for
users that use some cloud postgres.

Then those people can use the Standard SQL syntax. I am strongly
against polluting PostgreSQL because of what third party vendors do and
do not allow on their platforms.
--
Vik Fearing

út 15. 8. 2023 v 8:04 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

On 8/15/23 07:53, Pavel Stehule wrote:

út 15. 8. 2023 v 7:48 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

On 8/14/23 15:37, Pavel Stehule wrote:

po 14. 8. 2023 v 15:09 odesílatel Erik Rijkers <er@xs4all.nl> napsal:

I think so this can be +/- 40 lines of C code

It seems to me like a good candidate for an extension.

Unfortunately, these small extensions have zero chance to be available

for

users that use some cloud postgres.

Then those people can use the Standard SQL syntax. I am strongly
against polluting PostgreSQL because of what third party vendors do and
do not allow on their platforms.

ok