public inbox for [email protected]
help / color / mirror / Atom feedFrom: jian he <[email protected]>
To: Amit Langote <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Erik Rijkers <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: remaining sql/json patches
Date: Mon, 22 Jan 2024 23:46:15 +0800
Message-ID: <CACJufxEe4nXHAfHJXa8UgHy5qZ35H+eomVE0tqOE_Euk=aE7rQ@mail.gmail.com> (raw)
In-Reply-To: <CA+HiwqGsYmyHy8EZA42JoW3ywGRFDGYQWz6X6-c_MG9y9+AkEA@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CA+HiwqEZP-ybbvTZ_2gKJD2p60hvDkL9-UsoKrzirZkwdV60Ng@mail.gmail.com>
<CACJufxFTRw_o-Fey-Km1uavuUAZagXFgQLf8DsMoT_qU4AU8hw@mail.gmail.com>
<CA+HiwqGsYmyHy8EZA42JoW3ywGRFDGYQWz6X6-c_MG9y9+AkEA@mail.gmail.com>
On Mon, Jan 22, 2024 at 10:28 PM Amit Langote <[email protected]> wrote:
>
> > based on v35.
> > Now I only applied from 0001 to 0007.
> > For {DEFAULT expression ON EMPTY} | {DEFAULT expression ON ERROR}
> > restrict DEFAULT expression be either Const node or FuncExpr node.
> > so these 3 SQL/JSON functions can be used in the btree expression index.
>
> I'm not really excited about adding these restrictions into the
> transformJsonFuncExpr() path. Index or any other code that wants to
> put restrictions already have those in place, no need to add them
> here. Moreover, by adding these restrictions, we might end up
> preventing users from doing useful things with this like specify
> column references. If there are semantic issues with allowing that,
> we should discuss them.
>
after applying v36.
The following index creation and query operation works. I am not 100%
sure about these cases.
just want confirmation, sorry for bothering you....
drop table t;
create table t(a jsonb, b int);
insert into t select '{"hello":11}',1;
insert into t select '{"hello":12}',2;
CREATE INDEX t_idx2 ON t (JSON_query(a, '$.hello1' RETURNING int
default b + random() on error));
CREATE INDEX t_idx3 ON t (JSON_query(a, '$.hello1' RETURNING int
default random()::int on error));
SELECT JSON_query(a, '$.hello1' RETURNING int default ret_setint() on
error) from t;
SELECT JSON_query(a, '$.hello1' RETURNING int default sum(b) over()
on error) from t;
SELECT JSON_query(a, '$.hello1' RETURNING int default sum(b) on
error) from t group by a;
but the following cases will fail related to index and default expression.
create table zz(a int, b int);
CREATE INDEX zz_idx1 ON zz ( (b + random()::int));
create table ssss(a int, b int default ret_setint());
create table ssss(a int, b int default sum(b) over());
view thread (27+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: remaining sql/json patches
In-Reply-To: <CACJufxEe4nXHAfHJXa8UgHy5qZ35H+eomVE0tqOE_Euk=aE7rQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox