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 1hpssV-0003Pa-H6 for pgsql-sql@arkaria.postgresql.org; Tue, 23 Jul 2019 11:22:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hpssT-0003D8-QG for pgsql-sql@arkaria.postgresql.org; Tue, 23 Jul 2019 11:22:41 +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 1hpssT-00032p-2D for pgsql-sql@lists.postgresql.org; Tue, 23 Jul 2019 11:22:41 +0000 Received: from mx07-00157001.pphosted.com ([62.209.51.8] helo=mx08-00157001.pphosted.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1hpssK-00009M-8g for pgsql-sql@lists.postgresql.org; Tue, 23 Jul 2019 11:22:38 +0000 Received: from pps.filterd (m0042436.ppops.net [127.0.0.1]) by mx07-00157001.pphosted.com (8.16.0.42/8.16.0.42) with SMTP id x6NBM8Nh020016 for ; Tue, 23 Jul 2019 13:22:29 +0200 Received: from hub.temenos.com ([212.203.116.80]) by mx07-00157001.pphosted.com with ESMTP id 2tuwe98uu5-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128 verify=NOT) for ; Tue, 23 Jul 2019 13:22:28 +0200 Received: from GVAEXCHMB3.temenosgroup.com (10.42.5.130) by GVAEXCHMB3.temenosgroup.com (10.42.5.130) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.1713.5; Tue, 23 Jul 2019 13:22:28 +0200 Received: from NAM03-CO1-obe.outbound.protection.outlook.com (104.47.40.51) by gvaexchmb3.temenos.com (10.42.5.130) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256) id 15.1.1713.5 via Frontend Transport; Tue, 23 Jul 2019 13:22:28 +0200 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=AMK7MZ2RRPC/hHV6ZX1zO/PHvRmpaKjLoePCdUVpWPmAcR3fAjvaBXvixcEDgOV0GSmJc/8TgEt5o3CosGw+hXjMDQ0yyWDjyAZfyQbBV4h2hhk6GFe8RRd0+k7zMzKznIIff9C/xvDxREmu1MjiGhe5fRmXko1r2N6vRoiqmdnqReKSTXcTRA5k5yPB44UiTnZbvEkfuB2fJ3auOjBG0rqCquarJWrKvMFnp0qD73I3mqwGJK82ZZKBu/1qSFWFgSDYeZ9PT0f5tEZefVYIxy/1yx8XUy3ox4LNzEOc3/kb98ttLPCh++LM6cuN+W3PMputwnwiXcbxkIh7G3gQAQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=FW4zjp2mUMPYJipgNEfYuTGQbmvSFbjk6ttsAwwH9Lc=; b=fDFzLjezsG08KwvWfyd3+RPoep6LVnN4XnSqeNGT4wJM3qse5WI4GXko2MZbXm5jDesFxJhEvZWPfCgf0Rp5KgW4HUPwA1qlkRQx96TTXQZgyxjfpGNCnavax0SnJLBIkc7UW7T4ZeWnsMjCQE7leMoqr5I889Y40t0/lhUV++fG4WJGByRxppm2yz/18BqfpFs9dEBhbR7A8RVTYsUEnHo5Qvkw400w2mxj37vQZ4PuISg5Hq6nCXdsfFkS+Io2hkMoMitQ5N6GO+GD6UWg4+537WycTvtkkd1TTQrFX7H68flHAIZcXENZaMhevN3HHaWB8Ni61HP1o6OpSNDJBw== ARC-Authentication-Results: i=1; mx.microsoft.com 1;spf=pass smtp.mailfrom=temenos.com;dmarc=pass action=none header.from=temenos.com;dkim=pass header.d=temenos.com;arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=temenosgroup.onmicrosoft.com; s=selector1-temenosgroup-onmicrosoft-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=FW4zjp2mUMPYJipgNEfYuTGQbmvSFbjk6ttsAwwH9Lc=; b=QKtgPgWOTe31/mbySVQUa2GH3HhG1Vp5mO/EknsT7aVnGBZvxOe3jB5CPoHv9R4H8vmyyXsTYg3eZVIMF7bltViK6Uelan6iJDSJpN3f/KA4lygjReNg+w6HIDM3pB/qYlKgRsgZ3LieYjx5n9rSW1HGK4+uXqe+Y2G2kMSo/S8= Received: from MN2PR04MB5806.namprd04.prod.outlook.com (20.178.255.74) by MN2PR04MB5744.namprd04.prod.outlook.com (20.179.21.96) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.2094.17; Tue, 23 Jul 2019 11:22:26 +0000 Received: from MN2PR04MB5806.namprd04.prod.outlook.com ([fe80::3cc5:9a2a:990e:c0b9]) by MN2PR04MB5806.namprd04.prod.outlook.com ([fe80::3cc5:9a2a:990e:c0b9%7]) with mapi id 15.20.2115.005; Tue, 23 Jul 2019 11:22:26 +0000 From: Rick Vincent To: "pgsql-sql@lists.postgresql.org" Subject: Implicit typecast behavior Thread-Topic: Implicit typecast behavior Thread-Index: AdVBRg/nIUxdoYcFRY2p6EWtJ5zvEw== Date: Tue, 23 Jul 2019 11:22:25 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [144.85.183.117] x-ms-publictraffictype: Email x-ms-office365-filtering-correlation-id: 9064701a-857a-44be-d113-08d70f600a5b x-microsoft-antispam: BCL:0;PCL:0;RULEID:(2390118)(7020095)(4652040)(8989299)(5600148)(711020)(4605104)(1401327)(4534185)(4627221)(201703031133081)(201702281549075)(8990200)(2017052603328)(7193020);SRVR:MN2PR04MB5744; x-ms-traffictypediagnostic: MN2PR04MB5744: x-ms-exchange-purlcount: 2 x-microsoft-antispam-prvs: x-ms-oob-tlc-oobclassifiers: OLM:10000; x-forefront-prvs: 0107098B6C x-forefront-antispam-report: SFV:NSPM;SFS:(10019020)(346002)(39860400002)(376002)(136003)(366004)(396003)(199004)(189003)(66446008)(64756008)(66476007)(66946007)(66556008)(86362001)(74316002)(5660300002)(68736007)(66574012)(66066001)(7696005)(5640700003)(25786009)(6436002)(2501003)(478600001)(6306002)(790700001)(76116006)(554214002)(6116002)(54896002)(9326002)(7736002)(3480700005)(7116003)(9686003)(71200400001)(71190400001)(52536014)(55016002)(476003)(316002)(14454004)(26005)(53936002)(6506007)(256004)(14444005)(6916009)(2906002)(5024004)(99286004)(33656002)(8676002)(102836004)(2351001)(8936002)(486006)(3846002)(81166006)(186003)(81156014);DIR:OUT;SFP:1102;SCL:1;SRVR:MN2PR04MB5744;H:MN2PR04MB5806.namprd04.prod.outlook.com;FPR:;SPF:None;LANG:en;PTR:InfoNoRecords;A:1;MX:1; received-spf: None (protection.outlook.com: temenos.com does not designate permitted sender hosts) x-ms-exchange-senderadcheck: 1 x-microsoft-antispam-message-info: D7RREsAfA8T8f6nlUsQ2t6F969ohzzVmmF5+vinhFfzKbrpL0EPMXNIZLezvA2BhEDCRYrTmQelM4aRB4a3LO0BAtVEmM528M9CEEapnL983+SnOZJr2ldkrjtzzXiJ679kBF9YiC+3OiZkV5SN4AL+EqXRAxSvhQpt0iXBH5HSGZUa1aS8MMXluF2z/usJY1tmxX1NER/RiqpsVQ0wYUnLEf/FWjlEA9zdVHr2psgjhqhvmt2YjOem+01nx6O6c17gEMgRrDDZvuKQlKqGxe5z1Y6xEznKu/S9E1W2pvxjL1MdSXCX2Z6RkzJSLAdqFn2fe3/2oxuhYHz7nQLmCEDksU6ifMDHKRGuTFvsizZgnmezub8+xsqvfzGPBYhJFMHe5/vab2xxbJXMvweTNMeS/ZJQhJbtc+2kVg0vQvhM= Content-Type: multipart/alternative; boundary="_000_MN2PR04MB58068FFFAC2102C68FAF1945C1C70MN2PR04MB5806namp_" MIME-Version: 1.0 X-MS-Exchange-CrossTenant-Network-Message-Id: 9064701a-857a-44be-d113-08d70f600a5b X-MS-Exchange-CrossTenant-originalarrivaltime: 23 Jul 2019 11:22:25.8392 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: d5d2540f-f60a-45ad-86a9-e2e792ee6669 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: rvincent@temenos.com X-MS-Exchange-Transport-CrossTenantHeadersStamped: MN2PR04MB5744 X-OriginatorOrg: temenos.com X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:5.22.84,1.0.8 definitions=2019-07-23_05:2019-07-23,2019-07-23 signatures=0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_MN2PR04MB58068FFFAC2102C68FAF1945C1C70MN2PR04MB5806namp_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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_MN2PR04MB58068FFFAC2102C68FAF1945C1C70MN2PR04MB5806namp_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

 

I am looking for a way to make postgresql function a= s 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 N= UMERIC) > CAST (12 AS NUMERIC)

 

But I want it to be done implicitly like other datab= ases.  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 tonum= eric(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<= /o:p>

 

Because the numeric is being passed most likely.&nbs= p; 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 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_MN2PR04MB58068FFFAC2102C68FAF1945C1C70MN2PR04MB5806namp_--