Re: [SQL] Fw: Whats happen here? - Mailing list pgsql-sql

drop function char_eq_varchar(bpchar,varchar);
create function char_eq_varchar(bpchar,varchar) returns bool as
'
declare
 i2 text;
 i1 text;
begin
 i1:= trim($1);
 i2:= $2;
 if i1 = i2 then
 return TRUE;
 else
 return FALSE;
 end if;
end;
' language 'plpgsql';
 

drop operator = (bpchar,"varchar");
create operator = (
        leftarg=bpchar,
        rightarg="varchar",
        procedure=char_eq_varchar,
        commutator='=',
        negator='<>',
        restrict=eqsel,
        join=eqjoinsel
        );

drop table x;
drop table y;
create table x(a char(20));
create table y(a varchar(5));
insert into x values('a');
insert into y values('a');
select * from x, y where x.a = y.a;
a                   |a
--------------------+-
a                   |a
(1 row)
 

Jose'
 

Nikolay Mijaylov ha scritto:

> Whats happen here?
> -------------------------
>
> root=> \dt
>
> Database    = root
>  +------------------+----------------------------------+----------+
>  |  Owner           |             Relation             |   Type   |
>  +------------------+----------------------------------+----------+
>  | root             | x                                | table    |
>  | root             | y                                | table    |
>  +------------------+----------------------------------+----------+
> root=> \d x
>
> Table    = x
>
+----------------------------------+----------------------------------+-----
> --+
> |              Field               |              Type                |
> Length|
>
+----------------------------------+----------------------------------+-----
> --+
> | a                                | char()                           |
> 20 |
>
+----------------------------------+----------------------------------+-----
> --+
> root=> \d y
>
> Table    = y
>
+----------------------------------+----------------------------------+-----
> --+
> |              Field               |              Type                |
> Length|
>
+----------------------------------+----------------------------------+-----
> --+
> | a                                | varchar()                        |
> 5 |
>
+----------------------------------+----------------------------------+-----
> --+
> root=>select * from x a, y b where a.a = b.a;
> ERROR:  There is more than one possible operator '=' for types 'bpchar'
and
> 'varchar'
>         You will have to retype this query using an explicit cast
>
>
>
> root=> select * from x a, y b where text(a.a) = text(b.b);
>ERROR:  There is more than one possible operator '=' for types 'bpchar' and
'varchar'
>        You will have to retype this query using an explicit cast
>
> --------------------------------------------------------------
> The reboots are for hardware upgrades!
> "http://www.nmmm.nu; <nmmm@nmmm.nu>
>
>

************