Re: Suggestion: optionally return default value instead of error on failed cast

On 1/4/22 22:17, Corey Huinker wrote:
>
> currently a failed cast throws an error. It would be useful to have a
> way to get a default value instead.
>
>
> I've recently encountered situations where this would have been
> helpful. Recently I came across some client code:
>
> CREATE OR REPLACE FUNCTION is_valid_json(str text) RETURNS boolean
> LANGUAGE PLPGSQL
> AS $$
> DECLARE
>     j json;
> BEGIN
>     j := str::json;
>     return true;
> EXCEPTION WHEN OTHERS THEN return false;
> END
> $$;
>
>
> This is a double-bummer. First, the function discards the value so we
> have to recompute it, and secondly, the exception block prevents the
> query from being parallelized.

This particular case is catered for in the SQL/JSON patches which
several people are currently reviewing:

andrew=# select 'foo' is json;
 ?column?
----------
 f
(1 row)

andrew=# select '"foo"' is json;
 ?column?
----------
 t
(1 row)

cheers

andrew

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