Horton

Re: SQL/JSON features for v15

Nikita Glukhov <n.gluhov@postgrespro.ru>

View thread View on postgr.es
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
To: Andrew Dunstan <andrew@dunslane.net>, "Jonathan S. Katz" <jkatz@postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>, Andres Freund <andres@anarazel.de>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Michael Paquier <michael@paquier.xyz>, John Naylor <john.naylor@enterprisedb.com>, Amit Langote <amitlangote09@gmail.com>
Date: 2022-08-26T20:11:14Z
Lists: pgsql-hackers

Attachments

Hi,

On 26.08.2022 22:25, Andrew Dunstan wrote:
> On 2022-08-24 We 20:05, Nikita Glukhov wrote:
>> v8 - is a highly WIP patch, which I failed to finish today.
>> Even some test cases fail now, and they simply show unfinished
>> things like casts to bytea (they can be simply removed) and missing
>> safe input functions.
> Thanks for your work, please keep going.

I have completed in v9 all the things I previously planned:

  - Added missing safe I/O and type conversion functions for
    datetime, float4, varchar, bpchar.  This introduces a lot
    of boilerplate code for returning errors and also maybe
    adds some overhead.

  - Added JSON_QUERY coercion to UTF8 bytea using pg_convert_to().

  - Added immutability checks that were missed with elimination
    of coercion expressions.
    Coercions text::datetime, datetime1::datetime2 and even
    datetime::text for some datetime types are mutable.
    datetime::text can be made immutable by passing ISO date
    style into output functions (like in jsonpath).

  - Disabled non-Const expressions in DEFAULT ON EMPTY in non
    ERROR ON ERROR case.  Non-constant expressions are tried to
    evaluate into Const directly inside transformExpr().
    Maybe it would be better to simply remove DEFAULT ON EMPTY.


It is possible to easily split this patch into several subpatches,
I will do it if needed.

-- 
Nikita Glukhov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company

Commits

  1. JSON_TABLE: Add support for NESTED paths and columns

  2. Add basic JSON_TABLE() functionality

  3. Add SQL/JSON query functions

  4. Add soft error handling to some expression nodes

  5. Adjust populate_record_field() to handle errors softly

  6. Refactor code used by jsonpath executor to fetch variables

  7. Add more SQL/JSON constructor functions

  8. SQL/JSON: support the IS JSON predicate

  9. SQL/JSON: add standard JSON constructor functions

  10. Revert SQL/JSON features

  11. Numeric error suppression in jsonpath