From: | Александър Шопов <ash(at)contact(dot)bg> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Getting back the autocast on non-character via CREATE CAST |
Date: | 2008-11-28 04:17:10 |
Message-ID: | 1227845830.4848.32.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Guy Rouillier | 2008-11-28 04:42:49 | Re: please cansel my subscription |
Previous Message | Qiang | 2008-11-28 01:42:47 | please cansel my subscription |