RE: Better performance no-throw conversion?

> -----Original Message-----
> From: Andrew Dunstan <andrew(at)dunslane(dot)net>
> Sent: Wednesday, September 8, 2021 13:31
> To: ldh(at)laurent-hasson(dot)com; pgsql-performance(at)postgresql(dot)org
> Subject: Re: Better performance no-throw conversion?
>
>
> On 9/8/21 1:17 PM, ldh(at)laurent-hasson(dot)com wrote:
> >
> > 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?
> >
> >
> >
>
>
> Not that I know of, but you could probably do this fairly simply in C.
>
>
> cheers
>
>
> andrew
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com

Hello Andrew,

I work across multiple platforms (windows, linux, multiple managed cloud versions...) and a C-based solution would be problematic for us.

Thank you,
Laurent.