Re: Proposal: is_castable

Hi

pá 3. 4. 2020 v 13:45 odesílatel Michał Wadas <michalwadas(at)gmail(dot)com>
napsal:

> Currently there is no way to check if CAST will succeed.
>
> Therefore I propose adding new function: is_castable
>
> SELECT is_castable('foo' as time) // false
> SELECT is_castable('123' as numeric) // true
> SELECT is_castable(1.5 as int) // true
> SELECT is_castable('1.5' as int) // false
>
> Many users write their own functions:
>
> https://stackoverflow.com/q/10306830/2446102 (11k views, ~25 upvotes)
> https://stackoverflow.com/q/36775814/2446102
> https://stackoverflow.com/a/16206123/2446102 (72k views, 70 upvotes)
> https://stackoverflow.com/q/2082686/2446102 (174k views, ~150 upvotes)
>
> Similar features are implemented in:
> - SQL Server (as TRY_CONVERT)
> - Oracle (as CONVERT([val] DEFAULT [expr] ON CONVERSION ERROR)
>
> I would love to implement it myself, but my knowledge of C is superficial.
>

It's is interesting feature - and implementation can be very easy - but
without enhancing type API this function can be pretty slow.

So there is a dilemma - simple implementation (few work) but possible very
negative performance impact under higher load due work with savepoints, or
much larger work (probably easy) without necessity to use safepoints.

Regards

Pavel

>
> Thanks,
> Michał Wadas
>
>