AW: Implicit typecast behavior - Mailing list pgsql-sql

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)

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.