Re: Error-safe user functions

On Tue, Dec 6, 2022 at 6:46 AM Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

>
> On 2022-12-05 Mo 20:06, Tom Lane wrote:
> > Andres Freund <andres(at)anarazel(dot)de> writes:
> >
> >> But perhaps it's even worth having such a function properly exposed:
> >> It's not at all rare to parse text data during ETL and quite often
> >> erroring out fatally is undesirable. As savepoints are undesirable
> >> overhead-wise, there's a lot of SQL out there that tries to do a
> >> pre-check about whether some text could be cast to some other data
> >> type. A function that'd try to cast input to a certain type without
> >> erroring out hard would be quite useful for that.
> > Corey and Vik are already talking about a non-error CAST variant.
>
>
> /metoo! :-)
>
>
> > Maybe we should leave this in abeyance until something shows up
> > for that? Otherwise we'll be making a nonstandard API for what
> > will probably ultimately be SQL-spec functionality. I don't mind
> > that as regression-test infrastructure, but I'm a bit less excited
> > about exposing it as a user feature.
> >
>
>
> I think a functional mechanism could be very useful. Who knows when the
> standard might specify something in this area?
>
>
>
Vik's working on the standard (he put the spec in earlier in this thread).
I'm working on implementing it on top of Tom's work, but I'm one patchset
behind at the moment.

Once completed, it should be leverage-able in several places, COPY being
the most obvious.

What started all this was me noticing that if I implemented TRY_CAST in
pl/pgsql with an exception block, then I wasn't able to use parallel query.