AW: Implicit typecast behavior

Why not create a view which does this?

Like this:

CREATE TABLE A(INFO VARCHAR(20));

CREATE VIEW A_VIEW AS SELECT INFO::int AS INFO FROM A;

insert into a (info) values ('12345');

select * from A_VIEW;

(Tested with PostgreSQL 10.9)

Mit freundlichen Grüßen
Stefan Sonnenberg-Carstens

Von: Rick Vincent [mailto:[email protected]]
Gesendet: Dienstag, 23. Juli 2019 13:22
An: [email protected]
Betreff: Implicit typecast behavior

Hi,

I am looking for a way to make postgresql function as Oracle and other databases do with implicit typecasts.  For example, in the query below:

SELECT RECID, RANK FROM MYTABLE WHERE RANK > 12

RANK is defined as a VARCHAR and will be implicitly cast to NUMBER, but when I run this in Postgresql I get an error.
No operator matches the given name and argument type(s). You might need to add explicit type casts.

I know it will work If I do:

SELECT RECID, RANK FROM MYTABLE WHERE CAST(RANK AS NUMERIC) > CAST (12 AS NUMERIC)

But I want it to be done implicitly like other databases.  I have tried the following

CREATE FUNCTION tonumeric(varchar)
  RETURNS numeric
  STRICT IMMUTABLE LANGUAGE SQL AS
'SELECT cast($1 as numeric);';

CREATE CAST (varchar AS numeric) WITH FUNCTION tonumeric(varchar) AS IMPLICIT;

But this query:
SELECT RECID, RANK FROM MYTABLE WHERE RANK > CAST (12 AS NUMERIC);
Returns the following.
SQL function "tonumeric" statement 1

Because the numeric is being passed most likely.  Is there a way to do this correctly such that "RANK" will be converted to NUMERIC without me explicitly having to CAST it to numeric?

Thanks,
Rick

The information in this e-mail and any attachments is confidential and may be legally privileged. It is intended solely for the addressee or addressees. Any use or disclosure of the contents of this e-mail/attachments by a not intended recipient is unauthorized and may be unlawful. If you have received this e-mail in error please notify the sender. Please note that any views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of TEMENOS. We recommend that you check this e-mail and any attachments against viruses. TEMENOS accepts no liability for any damage caused by any malicious code or virus transmitted by this e-mail.