Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hpsyz-0003eH-VT for pgsql-sql@arkaria.postgresql.org; Tue, 23 Jul 2019 11:29:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hpsyy-0001x0-JN for pgsql-sql@arkaria.postgresql.org; Tue, 23 Jul 2019 11:29:24 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hpsyy-0001wt-3l for pgsql-sql@lists.postgresql.org; Tue, 23 Jul 2019 11:29:24 +0000 Received: from mail.ophardt.com ([213.164.155.29]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1hpsyq-0000CL-H2 for pgsql-sql@lists.postgresql.org; Tue, 23 Jul 2019 11:29:22 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.ophardt.com (Postfix) with ESMTP id 46CB34046F; Tue, 23 Jul 2019 13:29:14 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at ophardt.com Received: from mail.ophardt.com ([127.0.0.1]) by localhost (ophardt-mail.gatworks.de [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id DLIttxzbnIhL; Tue, 23 Jul 2019 13:29:11 +0200 (CEST) Received: from OPHT402P.ophardt.com (unknown [80.146.177.140]) by mail.ophardt.com (Postfix) with ESMTPS id 8CDF14003C; Tue, 23 Jul 2019 13:29:11 +0200 (CEST) Received: from OPHT402P.ophardt.com (192.168.3.2) by OPHT402P.ophardt.com (192.168.3.2) with Microsoft SMTP Server (TLS) id 15.0.1320.4; Tue, 23 Jul 2019 13:29:07 +0200 Received: from OPHT402P.ophardt.com ([::1]) by OPHT402P.ophardt.com ([::1]) with mapi id 15.00.1320.000; Tue, 23 Jul 2019 13:29:07 +0200 From: "Sonnenberg-Carstens, Stefan" To: Rick Vincent , "pgsql-sql@lists.postgresql.org" Subject: AW: Implicit typecast behavior Thread-Topic: Implicit typecast behavior Thread-Index: AdVBRg/nIUxdoYcFRY2p6EWtJ5zvEwAAwmJg Date: Tue, 23 Jul 2019 11:29:06 +0000 Message-ID: <4e796a455d7e40d0a0b173c775dbd681@OPHT402P.ophardt.com> References: In-Reply-To: Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-ms-exchange-transport-fromentityheader: Hosted x-originating-ip: [10.37.11.120] x-tm-as-product-ver: SMEX-11.7.0.1055-8.500.1020-24788.002 x-tm-as-result: No--26.400200-5.000000-31 x-tm-as-user-approved-sender: No x-tm-as-user-blocked-sender: No Content-Type: multipart/alternative; boundary="_000_4e796a455d7e40d0a0b173c775dbd681OPHT402Pophardtcom_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_4e796a455d7e40d0a0b173c775dbd681OPHT402Pophardtcom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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=FC=DFen 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 data= bases 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 whe= n 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 N= UMERIC) 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 IMPLIC= IT; 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 explici= tly 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 addressee= s. Any use or disclosure of the contents of this e-mail/attachments by a no= t intended recipient is unauthorized and may be unlawful. If you have recei= ved this e-mail in error please notify the sender. Please note that any vie= ws 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 liabili= ty for any damage caused by any malicious code or virus transmitted by this= e-mail. --_000_4e796a455d7e40d0a0b173c775dbd681OPHT402Pophardtcom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Why not= create a view which does this?

&n= bsp;

Like th= is:

&n= bsp;

CREATE = TABLE A(INFO VARCHAR(20));

&n= bsp;

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

&n= bsp;

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

&n= bsp;

select = * from A_VIEW;

&n= bsp;

(Tested= with PostgreSQL 10.9)

&n= bsp;

Mit freundlichen Gr=FC=DFen

Stefan Sonnenberg-Carstens<= span lang=3D"EN-GB" style=3D"font-size:8.0pt;font-family:"Arial",= "sans-serif";color:#4D4D4D">

&n= bsp;

Von: Rick Vinc= ent [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 typecast= s.  For example, in the query below:

 

SELECT RECID, RANK FROM MYTABLE= WHERE RANK > 12

 

RANK is defined as a VARCHAR an= d will be implicitly cast to NUMBER, but when I run this in Postgresql I ge= t an error.

No operator matches the given n= ame 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 implic= itly like other databases.  I have tried the following

 

CREATE FUNCTION tonumeric(varch= ar)

  RETURNS numeric

  STRICT IMMUTABLE LA= NGUAGE SQL AS

'SELECT cast($1 as numeric);';<= o:p>

 

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&qu= ot; statement 1

 

Because the numeric is being pa= ssed most likely.  Is there a way to do this correctly such that ̶= 0;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 addressee= s. Any use or disclosure of the contents of this e-mail/attachments by a no= t intended recipient is unauthorized and may be unlawful. If you have received this e-mail in error please noti= fy the sender. Please note that any views or opinions presented in this e-m= ail are solely those of the author and do not necessarily represent those o= f 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.

--_000_4e796a455d7e40d0a0b173c775dbd681OPHT402Pophardtcom_--