The following bug has been logged online:
Bug reference: 1783
Logged by: Laurent HERVE
Email address: laurent-herve@club-internet.fr
PostgreSQL version: 8.0.1
Operating system: Linux Mandriva LE 2005 x86_64
Description: A function using char variables does not behave the same
way between 7.4 and 8.0
Details:
I am upgrading a 7.4 database to PostgreSQL 8.0.
During my tests, I found that the results of a function weren't like I
expected to.
I am using a 8.0.1 but I didn't find anything related to that problem on the
release note for later versions.
ez33=# select version();
version
----------------------------------------------------------------------------
--------------------------------------------------------------
PostgreSQL 8.0.1 on x86_64-mandrake-linux-gnu, compiled by GCC
x86_64-mandrake-linux-gnu-gcc (GCC) 3.4.3 (Mandrakelinux 10.2 3.4.3-7mdk)
(1 ligne)
my fonction is :
create or replace function ctb_remove_extra_spaces (text) returns text as '
declare
p_string alias for $1;
tmp_string text :='''';
cln_string text :=''''; /* cleaned string */
i integer :=0;
pre_car char(1) :='''';
car char(1) :='''';
a varchar;
begin
tmp_string:=trim(p_string);
for i in 1 .. length(tmp_string)
loop
car := substring(tmp_string, i ,1);
raise notice ''% / % % %'',i,pre_car,car,cln_string;
-- raise notice ''%'',car;
if (car = '' '' and pre_car <> '' '')
then
a:=ascii(car);
raise notice ''ASCII = %'',a;
cln_string:=cln_string||car;
end if;
if (car <> '' '')
then
cln_string:=cln_string||car;
end if;
pre_car:=car;
end loop;
return cln_string;
end;
' language 'plpgsql';
A select ctb_remove_extra_spaces(' abc defghi '); should give 'abc
defghi'
Running it on PG 8 gives :
ez33=# select ctb_remove_extra_spaces(' abc defghi ');
NOTICE: 1 / a
NOTICE: 2 / a b a
NOTICE: 3 / b c ab
NOTICE: 4 / c abc
NOTICE: ASCII = 0
NOTICE: 5 / abc
NOTICE: 6 / abc
NOTICE: 7 / d abc
NOTICE: 8 / d e abcd
NOTICE: 9 / e f abcde
NOTICE: 10 / f g abcdef
NOTICE: 11 / g h abcdefg
NOTICE: 12 / h i abcdefgh
ctb_remove_extra_spaces
-------------------------
abcdefghi
(1 ligne)
which is a wrong result.
But when I change the datatypes char(1) to varchar, I get :
ez33=# select ctb_remove_extra_spaces(' abc defghi ');
NOTICE: 1 / a
NOTICE: 2 / a b a
NOTICE: 3 / b c ab
NOTICE: 4 / c abc
NOTICE: ASCII = 32
NOTICE: 5 / abc
NOTICE: 6 / abc
NOTICE: 7 / d abc
NOTICE: 8 / d e abc d
NOTICE: 9 / e f abc de
NOTICE: 10 / f g abc def
NOTICE: 11 / g h abc defg
NOTICE: 12 / h i abc defgh
ctb_remove_extra_spaces
-------------------------
abc defghi
(1 ligne)
which is right.
Well it looks like a bug ... but probably I had better using varchar in my
applications...
