Re: proposal: jsonb_populate_array
út 15. 8. 2023 v 8:04 odesílatel Vik Fearing napsal: > On 8/15/23 07:53, Pavel Stehule wrote: > > út 15. 8. 2023 v 7:48 odesílatel Vik Fearing > > napsal: > > > >> On 8/14/23 15:37, Pavel Stehule wrote: > >>> po 14. 8. 2023 v 15:09 odesílatel Erik Rijkers 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 > -- > Vik Fearing > >
Re: proposal: jsonb_populate_array
On 8/15/23 07:53, Pavel Stehule wrote: út 15. 8. 2023 v 7:48 odesílatel Vik Fearing napsal: On 8/14/23 15:37, Pavel Stehule wrote: po 14. 8. 2023 v 15:09 odesílatel Erik Rijkers 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
Re: proposal: jsonb_populate_array
út 15. 8. 2023 v 5:12 odesílatel jian he 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
Re: proposal: jsonb_populate_array
út 15. 8. 2023 v 7:48 odesílatel Vik Fearing
napsal:
> On 8/14/23 15:37, Pavel Stehule wrote:
> > po 14. 8. 2023 v 15:09 odesílatel Erik Rijkers napsal:
> >
> >> Op 8/14/23 om 14:51 schreef Pavel Stehule:> po 14. 8. 2023 v 11:32
> >> odesílatel Alvaro Herrera
> >> > 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.
> --
> Vik Fearing
>
>
Re: proposal: jsonb_populate_array
On 8/14/23 15:37, Pavel Stehule wrote:
po 14. 8. 2023 v 15:09 odesílatel Erik Rijkers napsal:
Op 8/14/23 om 14:51 schreef Pavel Stehule:> po 14. 8. 2023 v 11:32
odesílatel Alvaro Herrera
> 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
Re: proposal: jsonb_populate_array
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
Re: proposal: jsonb_populate_array
\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.
Re: proposal: jsonb_populate_array
po 14. 8. 2023 v 15:47 odesílatel Chapman Flack
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
>
Re: proposal: jsonb_populate_array
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
Re: proposal: jsonb_populate_array
po 14. 8. 2023 v 15:09 odesílatel Erik Rijkers napsal:
> Op 8/14/23 om 14:51 schreef Pavel Stehule:> po 14. 8. 2023 v 11:32
> odesílatel Alvaro Herrera
> > 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
> Let's hope it gets submitted 17-ish, anyway
>
> Erik
>
>
>
>
>
>
Re: proposal: jsonb_populate_array
Op 8/14/23 om 14:51 schreef Pavel Stehule:> po 14. 8. 2023 v 11:32
odesílatel Alvaro Herrera
> 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
Re: proposal: jsonb_populate_array
po 14. 8. 2023 v 11:32 odesílatel Alvaro Herrera
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
> --
> Á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)
>
Re: 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)
proposal: jsonb_populate_array
Hi Now, there is no native functionality for conversion from json(b) value to some array. https://stackoverflow.com/questions/76894960/unable-to-assign-text-value-to-variable-in-pgsql/76896112#76896112 It should not be too hard to implement native function jsonb_populate_array jsonb_populate_array(anyarray, jsonb) returns anyarray Usage: select jsonb_populate_array(null::text[], '["cust_full_name","cust_email"]') Comments, notes? Regards Pavel
