AW: Implicit typecast behavior - Sonnenberg-Carstens, Stefan

agora inbox for pgsql-sql@postgresql.org  
help / color / mirror / Atom feed

From: Sonnenberg-Carstens, Stefan <ssonnenberg@ophardt.com>
To: Rick Vincent <rvincent@temenos.com>
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: AW: Implicit typecast behavior
Date: Tue, 23 Jul 2019 11:29:06 +0000
Message-ID: <4e796a455d7e40d0a0b173c775dbd681@OPHT402P.ophardt.com> (raw)
In-Reply-To: <MN2PR04MB58068FFFAC2102C68FAF1945C1C70@MN2PR04MB5806.namprd04.prod.outlook.com>
References: <MN2PR04MB58068FFFAC2102C68FAF1945C1C70@MN2PR04MB5806.namprd04.prod.outlook.com>

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:rvincent@temenos.com]
Gesendet: Dienstag, 23. Juli 2019 13:22
An: pgsql-sql@lists.postgresql.org
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.

Message-ID: <4e796a455d7e40d0a0b173c775dbd681@OPHT402P.ophardt.com>
Permalink:  ../4e796a455d7e40d0a0b173c775dbd681@OPHT402P.ophardt.com/
Also on:    postgresql.org/message-id/4e796a455d7e40d0a0b173c775dbd681@OPHT402P.ophardt.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: ssonnenberg@ophardt.com, rvincent@temenos.com, pgsql-sql@lists.postgresql.org
  Subject: Re: AW: Implicit typecast behavior
  In-Reply-To: <4e796a455d7e40d0a0b173c775dbd681@OPHT402P.ophardt.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox