Feedback-ID: ia2694551:Fastmail
Date: Thu, 7 Mar 2024 15:14:15 +0100
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: Amit Langote <amitlangote09@gmail.com>, Erik Rijkers <er@xs4all.nl>, 
	jian he <jian.universality@gmail.com>, Andres Freund <andres@anarazel.de>, 
	Andrew Dunstan <andrew@dunslane.net>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: remaining sql/json patches
Message-ID: <202403071414.mrlp64mw4qex@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <e7959942-6181-4f58-8b78-214cb3ab6286@enterprisedb.com>
Archived-At: <https://www.postgresql.org/message-id/202403071414.mrlp64mw4qex%40alvherre.pgsql>
Precedence: bulk

On 2024-Mar-07, Tomas Vondra wrote:

> I was experimenting with the v42 patches, and I think the handling of ON
> EMPTY / ON ERROR clauses may need some improvement.

Well, the 2023 standard says things like

<JSON value function> ::=
  JSON_VALUE <left paren>
      <JSON API common syntax>
      [ <JSON returning clause> ]
      [ <JSON value empty behavior> ON EMPTY ]
      [ <JSON value error behavior> ON ERROR ]
  <right paren>

which implies that if you specify it the other way around, it's a syntax
error.

> I'm not sure what the SQL standard says about this, but it seems other
> databases don't agree on the order. Is there a particular reason to
> not allow both orderings?

I vaguely recall that trying to also support the other ordering leads to
having more rules.  Now maybe we do want that because of compatibility
with other DBMSs, but frankly at this stage I wouldn't bother.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"I am amazed at [the pgsql-sql] mailing list for the wonderful support, and
lack of hesitasion in answering a lost soul's question, I just wished the rest
of the mailing list could be like this."                               (Fotis)
              https://postgr.es/m/200606261359.k5QDxE2p004593@auth-smtp.hol.gr