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

On Thu, Jan 6, 2022 at 12:18 PM Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

>
> 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:
>
>
That's great to know, but it would still be parsing the json twice, once to
learn that it is legit json, and once to get the casted value.

Also, I had a similar issue with type numeric, so having generic "x is a
type_y" support would essentially do everything that a try_catch()-ish
construct would need to do, and be more generic.