Thread: Getting back the autocast on non-character via CREATE CAST

Getting back the autocast on non-character via CREATE CAST

From
Александър Шопов
Date:
Hi everyone,
In pg 8.2 to 8.3 transition there is a new behavior: Non-character data
types are no longer automatically cast to TEXT

I understand the reasons, however while we manage to migrate the our
system, is there a way to get back this behavior? I searched the
internet and the mailing lists but I found no other solution but
installing back 8.2.

I tried to do the following:

create table test (i integer);

insert into test values (1);

select * from test where i = (case when '0'<>'' then '1' else null end);


ERROR:  operator does not exist: integer = text
LINE 1: select * from test where i = (case when '0'<>'' then '1' els...
                                   ^
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.


OK, let's try a workaround:

create function text2integer(in text) returns integer as 'select
$1::integer' language sql immutable returns null on null input;

select text2integer('2');
 text2integer
--------------
            2
(1 row)

The function works. Now let us create a cast:

create cast ( text AS integer) with function text2integer (in text) AS
implicit;

Now when I do:
select * from test where i = (case when '0'<>'' then '1' else null end);

I get a multitude of the following:

SQL function "text2integer" statement 1

I also get multitude of the same when I do:

select count(*) from test where i = (case when '0'<>'' then '1' else
null end);

I tried also different permutations with text2integer, varchar <->
integer, AS ASSIGNMENT vs. AS IMPLICIT but I can never get back the
behavior of 8.2.

Is there really no work around? Is the non-character data autocast so
intrinsic that there is no way of getting back the previous behavior?

Kind regards:
al_shopov


Re: Getting back the autocast on non-character via CREATE CAST

From
Adrian Klaver
Date:
On Thursday 27 November 2008 8:17:10 pm Александър Шопов wrote:
> Hi everyone,
> In pg 8.2 to 8.3 transition there is a new behavior: Non-character data
> types are no longer automatically cast to TEXT
>
> I understand the reasons, however while we manage to migrate the our
> system, is there a way to get back this behavior? I searched the
> internet and the mailing lists but I found no other solution but
> installing back 8.2.
>
> I tried to do the following:
>
> create table test (i integer);
>
> insert into test values (1);
>
> select * from test where i = (case when '0'<>'' then '1' else null end);

Try:
select * from test where i = (case when '0'<>'' then '1' else null end)::int;

>
>
> ERROR:  operator does not exist: integer = text
> LINE 1: select * from test where i = (case when '0'<>'' then '1' els...
>                                    ^
> HINT:  No operator matches the given name and argument type(s). You
> might need to add explicit type casts.
>
>


>
> Kind regards:
> al_shopov



--
Adrian Klaver
aklaver@comcast.net