MIME-Version: 1.0
References: <202401181146.fuoeskfzriq7@alvherre.pgsql>
 <202401181711.qxjxpnl3ohnw@alvherre.pgsql>
 <CA+HiwqEZP-ybbvTZ_2gKJD2p60hvDkL9-UsoKrzirZkwdV60Ng@mail.gmail.com>
 <CACJufxFTRw_o-Fey-Km1uavuUAZagXFgQLf8DsMoT_qU4AU8hw@mail.gmail.com>
 <CA+HiwqGsYmyHy8EZA42JoW3ywGRFDGYQWz6X6-c_MG9y9+AkEA@mail.gmail.com>
 <CACJufxEe4nXHAfHJXa8UgHy5qZ35H+eomVE0tqOE_Euk=aE7rQ@mail.gmail.com>
In-Reply-To: 
 <CACJufxEe4nXHAfHJXa8UgHy5qZ35H+eomVE0tqOE_Euk=aE7rQ@mail.gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Tue, 23 Jan 2024 16:51:55 +0800
Message-ID: 
 <CACJufxGOJKDguV-wX6ceHFi7XQKJkRr7a7BUBsC_g8vAos63hw@mail.gmail.com>
Subject: Re: remaining sql/json patches
To: Amit Langote <amitlangote09@gmail.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>,
 Andres Freund <andres@anarazel.de>,
	Erik Rijkers <er@xs4all.nl>, Andrew Dunstan <andrew@dunslane.net>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: multipart/mixed; boundary="00000000000071d20d060f9912f6"
Archived-At: 
 <https://www.postgresql.org/message-id/CACJufxGOJKDguV-wX6ceHFi7XQKJkRr7a7BUBsC_g8vAos63hw%40mail.gmail.com>
Precedence: bulk

--00000000000071d20d060f9912f6
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Jan 22, 2024 at 11:46=E2=80=AFPM jian he <jian.universality@gmail.c=
om> wrote:
>
> On Mon, Jan 22, 2024 at 10:28=E2=80=AFPM Amit Langote <amitlangote09@gmai=
l.com> 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 ind=
ex.
> >
> > 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;

I forgot to attach ret_setint defition.

create or replace function ret_setint() returns setof integer as
$$
begin
    -- perform pg_sleep(0.1);
    return query execute 'select 1 union all select 1';
end;
$$
language plpgsql IMMUTABLE;

-----------------------------------------
In the function transformJsonExprCommon, we have
`JsonExpr   *jsexpr =3D makeNode(JsonExpr);`
then the following 2 assignments are not necessary.

/* Both set in the caller. */
jsexpr->result_coercion =3D NULL;
jsexpr->omit_quotes =3D false;

So I removed it.

JSON_VALUE OMIT QUOTES by default, so I set it accordingly.
I also changed coerceJsonFuncExprOutput accordingly

--00000000000071d20d060f9912f6
Content-Type: application/octet-stream;
	name="v1-0001-minor-refactor-transformJsonFuncExpr.based_on_v36"
Content-Disposition: attachment;
	filename="v1-0001-minor-refactor-transformJsonFuncExpr.based_on_v36"
Content-Transfer-Encoding: base64
Content-ID: <f_lrq485ue0>
X-Attachment-Id: f_lrq485ue0

RnJvbSAxZGU1YTFmYWI1ZGU2NDQ3OTM2ZjA2MmNmYTkyMzFjODg5MGY5NzIxIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBqaWFuIGhlIDxqaWFuLnVuaXZlcnNhbGl0eUBnbWFpbC5jb20+
CkRhdGU6IFR1ZSwgMjMgSmFuIDIwMjQgMTY6MzE6MTIgKzA4MDAKU3ViamVjdDogW1BBVENIIHYx
IDEvMV0gbWlub3IgcmVmYWN0b3IgdHJhbnNmb3JtSnNvbkZ1bmNFeHByCgpKU09OX1ZBTFVFIGJ5
IGRlZmF1bHQgaXMgb21pdF9xdW90ZSwgaG93ZXZlciB0cmFuc2Zvcm1Kc29uRXhwckNvbW1vbiB3
aWxsCmluaXRpYWxpemUgaXQgdG8gZmFsc2UgdmlhICdtYWtlTm9kZShKc29uRXhwciknLgpTbyBz
ZXQgaXQgZXhwbGljaXRseS4Kbm8gbmVlZCB0byBzZXQganNleHByLT5yZXN1bHRfY29lcmNpb24g
YXQgdHJhbnNmb3JtSnNvbkV4cHJDb21tb24sIHNpbmNlIG1ha2VOb2RlIHdpbGwKbWFrZSBqc2V4
cHItPnJlc3VsdF9jb2VyY2lvbiBiZSBOVUxMOwoKQWNjb3JkaW5nbHksIHdlIGFsc28gbmVlZCBy
ZWZhY3RvciBmdW5jdGlvbiBjb2VyY2VKc29uRnVuY0V4cHJPdXRwdXQuCm1ha2UgSlNPTl9WQUxV
RSByZXN1bHRfY29lcmNpb24gdmlhIENhc2VUZXN0RXhwci4KLS0tCiBzcmMvYmFja2VuZC9wYXJz
ZXIvcGFyc2VfZXhwci5jIHwgOCArKystLS0tLQogMSBmaWxlIGNoYW5nZWQsIDMgaW5zZXJ0aW9u
cygrKSwgNSBkZWxldGlvbnMoLSkKCmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9wYXJzZXIvcGFy
c2VfZXhwci5jIGIvc3JjL2JhY2tlbmQvcGFyc2VyL3BhcnNlX2V4cHIuYwppbmRleCBiMTkwOGMz
Ni4uM2YwM2FlZjAgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL3BhcnNlci9wYXJzZV9leHByLmMK
KysrIGIvc3JjL2JhY2tlbmQvcGFyc2VyL3BhcnNlX2V4cHIuYwpAQCAtNDM1OCw2ICs0MzU4LDcg
QEAgdHJhbnNmb3JtSnNvbkZ1bmNFeHByKFBhcnNlU3RhdGUgKnBzdGF0ZSwgSnNvbkZ1bmNFeHBy
ICpmdW5jKQogCQkJCWpzZXhwci0+cmV0dXJuaW5nLT50eXBpZCA9IFRFWFRPSUQ7CiAJCQkJanNl
eHByLT5yZXR1cm5pbmctPnR5cG1vZCA9IC0xOwogCQkJfQorCQkJanNleHByLT5vbWl0X3F1b3Rl
cyA9IHRydWU7CiAJCQlqc2V4cHItPnJlc3VsdF9jb2VyY2lvbiA9IGNvZXJjZUpzb25GdW5jRXhw
ck91dHB1dChwc3RhdGUsIGpzZXhwcik7CiAKIAkJCS8qCkBAIC00NDI2LDEwICs0NDI3LDYgQEAg
dHJhbnNmb3JtSnNvbkV4cHJDb21tb24oUGFyc2VTdGF0ZSAqcHN0YXRlLCBKc29uRnVuY0V4cHIg
KmZ1bmMsCiAKIAlqc2V4cHItPmZvcm1hdCA9IGZ1bmMtPmNvbnRleHRfaXRlbS0+Zm9ybWF0Owog
Ci0JLyogQm90aCBzZXQgaW4gdGhlIGNhbGxlci4gKi8KLQlqc2V4cHItPnJlc3VsdF9jb2VyY2lv
biA9IE5VTEw7Ci0JanNleHByLT5vbWl0X3F1b3RlcyA9IGZhbHNlOwotCiAJcGF0aHNwZWMgPSB0
cmFuc2Zvcm1FeHByUmVjdXJzZShwc3RhdGUsIGZ1bmMtPnBhdGhzcGVjKTsKIAogCWpzZXhwci0+
cGF0aF9zcGVjID0KQEAgLTQ1MTAsNyArNDUwNyw4IEBAIGNvZXJjZUpzb25GdW5jRXhwck91dHB1
dChQYXJzZVN0YXRlICpwc3RhdGUsIEpzb25FeHByICpqc2V4cHIpCiAJICogVXNlIGEgSnNvbkNv
ZXJjaW9uIG5vZGUgdG8gaW1wbGVtZW50IGEgbm9uLWRlZmF1bHQgUVVPVEVTIG9yIFdSQVBQRVIK
IAkgKiBiZWhhdmlvci4KIAkgKi8KLQlpZiAoanNleHByLT5vbWl0X3F1b3RlcyB8fCBqc2V4cHIt
PndyYXBwZXIgIT0gSlNXX1VOU1BFQykKKwlpZiAoanNleHByLT5vcCA9PSBKU09OX1FVRVJZX09Q
ICYmCisJCShqc2V4cHItPm9taXRfcXVvdGVzIHx8IGpzZXhwci0+d3JhcHBlciAhPSBKU1dfVU5T
UEVDKSkKIAl7CiAJCUpzb25Db2VyY2lvbiAqY29lcmNpb24gPSBtYWtlSnNvbkNvZXJjaW9uKHJl
dHVybmluZyk7CiAKLS0gCjIuMzQuMQoK
--00000000000071d20d060f9912f6--