Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1] (may be forged)) by hub.org (8.9.3/8.9.3) with ESMTP id KAA47571; Tue, 7 Dec 1999 10:34:44 -0500 (EST) (envelope-from jose@sferacarta.com) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.9.3/8.9.3/NETTuno 4.1) with ESMTP id QAA03047; Tue, 7 Dec 1999 16:33:25 +0100 (MET) Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5] by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian)) id 11vMjn-0000GG-00; Tue, 7 Dec 1999 15:41:11 +0000 Message-ID: <384D1D3B.2BF89CBB@sferacarta.com> Date: Tue, 07 Dec 1999 15:44:12 +0100 From: jose soares X-Mailer: Mozilla 4.5 [it] (Win95; I) X-Accept-Language: it MIME-Version: 1.0 To: Nikolay Mijaylov CC: pgsql-general , pgsql-sql@postgresql.org Subject: Re: [SQL] Fw: Whats happen here? References: <002001bf3ffd$17309980$ce2a18c3@skillbrokers.bg> Content-Type: multipart/alternative; boundary="------------B22AAD1845D9DFF1D252D2A3" --------------B22AAD1845D9DFF1D252D2A3 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Try this: 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; > > > > > > ************ --------------B22AAD1845D9DFF1D252D2A3 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Try this:

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>
>
>

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

--------------B22AAD1845D9DFF1D252D2A3--