agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Tom Lane <tgl@sss.pgh.pa.us>
To: Rick Vincent <rvincent@temenos.com>
Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: Implicit typecast behavior
Date: Tue, 23 Jul 2019 10:31:05 -0400
Message-ID: <26465.1563892265@sss.pgh.pa.us> (raw)
In-Reply-To: <MN2PR04MB58068FFFAC2102C68FAF1945C1C70@MN2PR04MB5806.namprd04.prod.outlook.com>
References: <MN2PR04MB58068FFFAC2102C68FAF1945C1C70@MN2PR04MB5806.namprd04.prod.outlook.com>
Rick Vincent <rvincent@temenos.com> writes:
> I am looking for a way to make postgresql function as Oracle and other databases do with implicit typecasts.
I do not think this behavior is as widespread as you make it out to be.
It's certainly contrary to the SQL standard.
> 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
For the record, what you probably actually got was something like
ERROR: stack depth limit exceeded
HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT: SQL function "tonumeric" during startup
SQL function "tonumeric" statement 1
SQL function "tonumeric" statement 1
SQL function "tonumeric" statement 1
SQL function "tonumeric" statement 1
... lots and lots and lots of these
because the way you set that up, the function is simply an infinite
recursion. You told the system that the way to coerce varchar to numeric
is to call tonumeric(), so that's what it did --- including in the
cast inside the function itself.
You can actually make this work, if you forget the function and do
CREATE CAST (varchar AS numeric) WITH INOUT AS IMPLICIT;
"WITH INOUT" will work whenever the text representation of the source
value is acceptable as text input for the destination type, which
I assume is the behavior you were looking for.
However, I feel a bit like I've just handed a sharp object to a small
child. Many years of bitter experience have taught us that implicit
coercions are *dangerous* and best avoided, because they have a bad
habit of getting applied when you didn't expect them to, causing
surprising silent changes in query behavior.
You can limit the surprises by only allowing implicit casts that don't
cross type categories, so that the source and target types have generally
the same semantics ... but varchar -> numeric doesn't meet that rule.
Here are a few examples culled from the bad old days when we still
had a lot of cross-category casts to text:
https://www.postgresql.org/message-id/flat/b42b73150702191339t71edd1bxa2510df0c4d75876%40mail.gmail....
https://www.postgresql.org/message-id/flat/45D4E5A7.9060702%40wykids.org
https://www.postgresql.org/message-id/flat/E1Bg5qd-0001E8-00%40ms2.city.ac.uk
(There are *lots* more in the archives, up till we got rid of those
casts circa 2007. I just listed a couple that I found by searching
for "implicit casts to text".)
regards, tom lane
view thread (4+ messages) latest in thread
Message-ID: <26465.1563892265@sss.pgh.pa.us>
Permalink: ../26465.1563892265@sss.pgh.pa.us/
Also on: postgresql.org/message-id/26465.1563892265@sss.pgh.pa.us
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: tgl@sss.pgh.pa.us, rvincent@temenos.com, pgsql-sql@lists.postgresql.org
Subject: Re: Implicit typecast behavior
In-Reply-To: <26465.1563892265@sss.pgh.pa.us>
* 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