Re: SQL/JSON: JSON_TABLE - Mailing list pgsql-hackers

From Matthias Kurz
Subject Re: SQL/JSON: JSON_TABLE
Date
Msg-id CAO=2mx4_ydDkOL7CkUe8ednK7tG+RH2RsAvFdfcR5KGjyp_=aA@mail.gmail.com
Whole thread Raw
In response to Re: SQL/JSON: JSON_TABLE  (Andrew Dunstan <andrew@dunslane.net>)
Responses Re: SQL/JSON: JSON_TABLE
List pgsql-hackers
Hi everyone!

I am watching this thread since quite a while and I am waiting eagerly a long time already that this feature finally lands in PostgreSQL.
Given that in around 2 weeks PostgreSQL 15 will go into feature freeze (in the last years that usually happened around the 8th of April AFAIK), is there any chance this will be committed? As far as I understand the patches are almost ready.

Sorry for the noise, I just wanted to draw attention that there are people out there looking forward to JSON_TABLE ;)

Thanks everyone for your fantastic work!
Matthias


On Sun, 13 Mar 2022 at 22:22, Andrew Dunstan <andrew@dunslane.net> wrote:

On 2/9/22 08:22, Himanshu Upadhyaya wrote:
> On Wed, Feb 2, 2022 at 12:44 AM Andrew Dunstan <andrew@dunslane.net> wrote:
>>
>> rebased with some review comments attended to.
> I am in process of reviewing these patches, initially, have started
> with 0002-JSON_TABLE-v55.patch.
> Tested many different scenarios with various JSON messages and these
> all are working as expected. Just one question on the below output.
>
> ‘postgres[1406146]=#’SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS
> (a int PATH '$.a' ERROR ON EMPTY)) jt;
>  a
> ---
>
> (1 row)
>
> ‘postgres[1406146]=#’SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS
> (a int PATH '$.a' ERROR ON ERROR)) jt;
>  a
> ---
>
> (1 row)
>
> is not "ERROR ON ERROR" is expected to give error?


I think I understand what's going on here. In the first example 'ERROR
ON EMPTY' causes an error condition, but as the default action for an
error condition is to return null that's what happens. To get an error
raised you would need to say 'ERROR ON EMPTY ERROR ON ERROR'. I don't
know if that's according to spec. It seems kinda screwy, arguably a POLA
violation, although that would hardly be a first for the SQL Standards
body.  But I'm speculating here, I'm not a standards lawyer.

In the second case it looks like there isn't really an error. There
would be if you used 'strict' in the path expression.


This whole area needs more documentation.


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com



pgsql-hackers by date:

Previous
From: Tatsuo Ishii
Date:
Subject: Re: [HACKERS] WIP aPatch: Pgbench Serialization and deadlock errors
Next
From: vignesh C
Date:
Subject: Re: Identify missing publications from publisher while create/alter subscription.