Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4

Hi

po 30. 8. 2021 v 2:44 odesílatel ldh@laurent-hasson.com <
ldh@laurent-hasson.com> napsal:

>
>
>
> At this point, I am not sure how to proceed except to rethink that
> toFloat() function and many other places where we use exceptions. We get
> such dirty data that I need a "safe" way to convert a string to float
> without throwing an exception. BTW, I tried other combinations in case
> there may have been some weird interactions with the ::REAL conversion
> operator, but nothing made any change. Could you recommend another approach
> off the top of your head? I could use regexes for testing etc... Or maybe
> there is another option like a no-throw conversion that's built in or in
> some extension that you may know of? Like the "SAFE." Prefix in BigQuery.
>

CREATE OR REPLACE FUNCTION safe_to_double_precision(t text)
RETURNS double precision AS $$
BEGIN
  IF $1 SIMILAR TO '[+-]?([0-9]*[.])?[0-9]+' THEN
    RETURN $1::double precision;
  ELSE
    RETURN NULL;
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

Regards

Pavel


>
> Thank you,
> Laurent.
>
>
>
>