Horton

Re: pgsql: Add more SQL/JSON constructor functions

jian he <jian.universality@gmail.com>

View thread View on postgr.es
From: jian he <jian.universality@gmail.com>
To: Amit Langote <amitlangote09@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Peter Eisentraut <peter@eisentraut.org>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2024-07-24T08:47:15Z
Lists: pgsql-hackers 
drop domain if exists djs;
create domain djs as jsonb check ( value <> '"11"' );
SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING djs keep quotes
DEFAULT '"11"' ON empty);
SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING djs omit quotes
DEFAULT '"11"' ON empty);
SELECT JSON_QUERY(jsonb '"11"', '$' RETURNING djs omit quotes DEFAULT
'"11"' ON empty);

SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING jsonb keep quotes
DEFAULT '"11"' ON empty);
SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING jsonb omit quotes
DEFAULT '"11"' ON empty);
SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING int4range omit quotes
DEFAULT '"[1,2]"'::jsonb ON empty);
SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING int4range keep quotes
DEFAULT '"[1,2]"'::jsonb ON empty);
SELECT JSON_value(jsonb '"aaa"', '$.a' RETURNING int4range  DEFAULT
'"[1,2]"'::jsonb ON empty);
----------------------------

I found out 2 issues for the above tests.
1. RETURNING types is jsonb/domain over jsonb, default expression does
not respect omit/keep quotes,
but other RETURNING types do. Maybe this will be fine.

2. domain over jsonb should fail just like domain over other types?
RETURNING djs keep quotes DEFAULT '"11"' ON empty
should fail as
ERROR:  could not coerce ON EMPTY expression (DEFAULT) to the RETURNING type
DETAIL:  value for domain djs violates check constraint "djs_check""



                            errcode(ERRCODE_CANNOT_COERCE),
                            errmsg("cannot cast behavior expression of
type %s to %s",
                                   format_type_be(exprType(expr)),
                                   format_type_be(returning->typid)),
                            errhint("You will need to cast the expression."),
                            parser_errposition(pstate, exprLocation(expr)));

maybe
errhint("You will need to explicitly cast the expression to type %s",
format_type_be(returning->typid))

Commits

  1. SQL/JSON: Avoid initializing unnecessary ON ERROR / ON EMPTY steps

  2. SQL/JSON: Fix default ON ERROR behavior for JSON_TABLE

  3. SQL/JSON: Fix JSON_TABLE() column deparsing

  4. Update comment about ExprState.escontext

  5. SQL/JSON: Fix casting for integer EXISTS columns in JSON_TABLE

  6. SQL/JSON: Some fixes to JsonBehavior expression casting

  7. SQL/JSON: Remove useless code in ExecInitJsonExpr()

  8. SQL/JSON: Respect OMIT QUOTES when RETURNING domains over jsonb

  9. SQL/JSON: Improve error-handling of JsonBehavior expressions

  10. SQL/JSON: Fix error-handling of some JsonBehavior expressions

  11. SQL/JSON: Rethink c2d93c3802b

  12. SQL/JSON: Always coerce JsonExpr result at runtime

  13. SQL/JSON: Fix coercion of constructor outputs to types with typmod