Implicit typecast behavior

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Implicit typecast behavior

Rick Vincent

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 | Threaded
Open this post in threaded view
|

AW: Implicit typecast behavior

Sonnenberg-Carstens, Stefan

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üßen

Stefan Sonnenberg-Carstens

 

Von: Rick Vincent [mailto:[hidden email]]
Gesendet: Dienstag, 23. Juli 2019 13:22
An: [hidden email]
Betreff: Implicit typecast behavior

 

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 | Threaded
Open this post in threaded view
|

Re: Implicit typecast behavior

Thomas Kellerer
In reply to this post by Rick Vincent
Rick Vincent schrieb am 23.07.2019 um 13:22:
>
> 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

I know that you probably don't want to hear this, but:

That is really bad coding style in Oracle just as well as in Postgres as well.

It was a bug waiting to happen in Oracle already - the query will fail if there is at least one row where RANK can not be converted to number.

The correct solution to that problem is to not store numbers in VARCHAR columns - full stop.
Don't do it. Not in Postgres not in any other database.

Do the right thing and convert that column to an integer column.

Thomas


Reply | Threaded
Open this post in threaded view
|

Re: Implicit typecast behavior

Tom Lane-2
In reply to this post by Rick Vincent
Rick Vincent <[hidden email]> 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.com
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


Reply | Threaded
Open this post in threaded view
|

RE: Implicit typecast behavior

Rick Vincent
Thanks Tom.  It works.  It might not be SQL standard to do this, but the major database vendors are doing implicit casting such as this.  It will probably work for us because everything is a VARCHAR unless a column is explicitly set to a different datatype held in a metadata dictionary.  We have a huge test suite so we will see.

Thanks for your help.
Rick

-----Original Message-----
From: Tom Lane <[hidden email]>
Sent: Tuesday, July 23, 2019 4:31 PM
To: Rick Vincent <[hidden email]>
Cc: [hidden email]
Subject: Re: Implicit typecast behavior

Rick Vincent <[hidden email]> 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.com
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



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.