Message-ID: <c04ff761-930b-4345-ae23-f78b8ee7ce32@postgrespro.ru>
Date: Mon, 16 Oct 2023 16:44:27 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: remaining sql/json patches
Content-Language: en-US
To: jian he <jian.universality@gmail.com>,
 Amit Langote <amitlangote09@gmail.com>
Cc: Nikita Malakhov <hukutoc@gmail.com>, Andres Freund <andres@anarazel.de>,
 Alvaro Herrera <alvherre@alvh.no-ip.org>,
 Andrew Dunstan <andrew@dunslane.net>, Erik Rijkers <er@xs4all.nl>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
References: 
 <CA+HiwqFJQdBQzTbSt6=S_3mmYf_pb2YoRNrhL1R0zzFz5Yja=w@mail.gmail.com>
 <202309281104.kmkr4v34ny2t@alvherre.pgsql>
 <CA+HiwqETtgH0ESDEEGDwTmazmtNeR7UsJS=BOoa=ghdYMo+Ufg@mail.gmail.com>
 <20231006214856.yie5dedhzrcb3qxx@alap3.anarazel.de>
 <CA+HiwqGdrSTsjyBcw66WE=vDke4B5fJj2zXBbXpv-npgzm4+rg@mail.gmail.com>
 <CA+HiwqEHWkGwiy3mKzjf0MaPU84LZh9PjtL3TDZuvF=0QLVz=w@mail.gmail.com>
 <CAN-LCVNNjsruFkAGHeF-9W0yTQKuELKAXsyYs3WDTe_SX+9ppg@mail.gmail.com>
 <CAN-LCVP=5QSmbndz4q+mNxddLa2y4XVOw+z7Dx=iUkegNM8Sjg@mail.gmail.com>
 <CA+HiwqGNmJ67n6BKAROEBHqv0faGA3GxU+vH1wO8x-m9i=hRoQ@mail.gmail.com>
 <CACJufxHLYQPw-7Wg=EYqnQPjhaB_dJGH+USsT0vP5FL3sxLX5Q@mail.gmail.com>
From: "Anton A. Melnikov" <a.melnikov@postgrespro.ru>
In-Reply-To: 
 <CACJufxHLYQPw-7Wg=EYqnQPjhaB_dJGH+USsT0vP5FL3sxLX5Q@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/c04ff761-930b-4345-ae23-f78b8ee7ce32%40postgrespro.ru>
Precedence: bulk

Hello!

On 16.10.2023 15:49, jian he wrote:
> add the following code after ExecEvalJsonExprCoercion if
> (!InputFunctionCallSafe(...) works, but seems like a hack.
> 
> if (!val_string)
> {
> *op->resnull = true;
> *op->resvalue = (Datum) 0;
> }

It seems the constraint should work here:

After

CREATE TABLE test (
	js text,
	i int,
	x jsonb DEFAULT JSON_QUERY(jsonb '[1,2]', '$[*]' WITH WRAPPER)
	CONSTRAINT test_constraint
		CHECK (JSON_QUERY(js::jsonb, '$.a' RETURNING char(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > 'a')
);

INSERT INTO test_jsonb_constraints VALUES ('[]');

one expected to see an error like that:

ERROR:  new row for relation "test" violates check constraint "test_constraint"
DETAIL:  Failing row contains ([], null, [1, 2]).

not "INSERT 0 1"

With best regards,

-- 
Anton A. Melnikov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company