Обсуждение: Proposal: is_castable

Поиск
Список
Период
Сортировка

Proposal: is_castable

От
Michał Wadas
Дата:
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:


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.

Thanks,
Michał Wadas

Re: Proposal: is_castable

От
Pavel Stehule
Дата:
Hi

pá 3. 4. 2020 v 13:45 odesílatel Michał Wadas <michalwadas@gmail.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:


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

Re: Proposal: is_castable

От
Tom Lane
Дата:
=?UTF-8?Q?Micha=C5=82_Wadas?= <michalwadas@gmail.com> writes:
> 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

What would you actually do with it?

> Similar features are implemented in:
> - SQL Server (as TRY_CONVERT)
> - Oracle (as CONVERT([val] DEFAULT [expr] ON CONVERSION ERROR)

Somehow, I don't think those have the semantics of what you suggest here.

I suspect you are imagining that you could write something like

CASE WHEN is_castable(x as y) THEN cast(x as y) ELSE ...

but that will not work.  The THEN condition has to pass parse analysis
whether or not execution will ever reach it.

            regards, tom lane

Re: Proposal: is_castable

От
Adam Brusselback
Дата:
>  What would you actually do with it?

I am one of the users of these do-it-yourself functions, and I use them in my ETL pipelines heavily.

For me, data gets loaded into a staging table, all columns text, and I run a whole bunch of validation queries 
on the data prior to it moving to the next stage in the pipeline, a strongly typed staging table, where more 
validations are performed. So I currently check each column type with my custom can_convert_sometype(text) 
functions, and if the row has any columns that cannot convert, it marks a boolean to ignore moving that row
to the next strongly typed table (thus avoiding the cast for those rows). 

For this ETL process, I need to give users feedback about why specific specific rows failed to be processed, so
each of those validations also logs an error message for the user for each row failing a specific validation.

So it's a two step process for me currently because of this, I would love if there was a better way to handle 
this type of work though, because my plpgsql functions using exception blocks are not exactly great 
for performance.

>> Similar features are implemented in:
>> - SQL Server (as TRY_CONVERT)
>> - Oracle (as CONVERT([val] DEFAULT [expr] ON CONVERSION ERROR)
>
> Somehow, I don't think those have the semantics of what you suggest here.  

Agreed that they aren't the same exact feature, but I would very much love the ability to both 
know "will this cast fail?", and also be able to "try and cast, but if it fails just put this value and don't error".

They both have uses IMO, and while having is_castable() functions built in would be great, I just want to
express my desire for something like the above feature in SQL Server or Oracle as well.


Re: Proposal: is_castable

От
Pavel Stehule
Дата:
Hi


So it's a two step process for me currently because of this, I would love if there was a better way to handle 
this type of work though, because my plpgsql functions using exception blocks are not exactly great 
for performance.

Probably we can for some important buildin types write method "is_valid", and this method can be called directly. For custom types or for types without this method, the solution based on exceptions can be used.

This should not be too much code, and can be fast for often used types.