Better performance no-throw conversion?

From: "ldh(at)laurent-hasson(dot)com" <ldh(at)laurent-hasson(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Better performance no-throw conversion?
Date: 2021-09-08 17:17:35
Message-ID: MN2PR15MB2560C23261EFFBFC9F437A5B85D49@MN2PR15MB2560.namprd15.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello,

Some databases such as SQLServer (try_cast) or BigQuery (safe.cast) offer not-throw conversion. In general, these tend to perform better than custom UDFs that catch exceptions and are also simpler to use. For example, in Postgres, I have a function that does the following:

CREATE OR REPLACE FUNCTION toFloat(str varchar, val real)
RETURNS real AS $$
BEGIN
RETURN case when str is null then val else str::real end;
EXCEPTION WHEN OTHERS THEN
RETURN val;
END;
$$ LANGUAGE plpgsql COST 1 IMMUTABLE;

I couldn't find a reference to such capabilities in Postgres and wondered if I missed it, and if not, is there any plan to add such a feature?

Thank you!
Laurent Hasson.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Andrew Dunstan 2021-09-08 17:31:01 Re: Better performance no-throw conversion?
Previous Message Stepan Yankevych 2021-09-07 18:05:42 Foreign table as partition - Non optimal aggregation plan