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.
