Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
| От | Pavel Stehule |
|---|---|
| Тема | Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4 |
| Дата | |
| Msg-id | CAFj8pRDPew1rg3c-WNxQmUugO0FQRyg3MTy67-qoUtGA=fwZ6Q@mail.gmail.com обсуждение исходный текст |
| Ответ на | RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4 ("ldh@laurent-hasson.com" <ldh@laurent-hasson.com>) |
| Список | pgsql-performance |
Hi
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;
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.
В списке pgsql-performance по дате отправления: