From: Rick Vincent <rvincent@temenos.com>
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Implicit typecast behavior
Date: Tue, 23 Jul 2019 11:22:25 +0000
Message-ID: <MN2PR04MB58068FFFAC2102C68FAF1945C1C70@MN2PR04MB5806.namprd04.prod.outlook.com> (raw)
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.
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: rvincent@temenos.com, pgsql-sql@lists.postgresql.org
Subject: Re: Implicit typecast behavior
In-Reply-To: <MN2PR04MB58068FFFAC2102C68FAF1945C1C70@MN2PR04MB5806.namprd04.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox