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

Hum, I wonder if it's necessary to create an operator?

I had a similar problem a while back, the failing operator was || the
concatenation operator, for exactly the same reason.

This is the answer I got from Ross Reedstrom (reedstrm@rice.edu) and it
worked a treat:

"> Stuart -
> I think this is a consequence of the internal representations of text
> and varchar and char being identical, so that the cast finctions think
> there's nothing to do.
> I think this is fixed in 6.5. A work around for 6.4 is to apply
> afunction that does nothing to the text: I've used btrim() in the past
> (since I usually want to get rid of trailing whitespace anyway:
>
>test=> select btrim(chromosome) || btrim(arm) as locus from experiment;
>locus
>-----
>22q
>17p
>(2 rows)"

This was PG 6.4 and perhaps things have changed but at least here you have
a 'generalised' solution for all operator between a bpchar and a varchar.

HTH,

Stuart.

On Tue, 7 Dec 1999, jose soares wrote:

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

Stuart C. G. Rison
Department of Biochemistry and Molecular Biology
6th floor, Darwin Building, University College London (UCL)
Gower Street, London, WC1E 6BT, United Kingdom
Tel. 0207 504 2303, Fax. 0207 380 7193
e-mail: rison@biochem.ucl.ac.uk