pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stuart Rison <rison@biochemistry.ucl.ac.uk>
To: jose soares <jose@sferacarta.com>
Cc: Nikolay Mijaylov <nmmm@nmmm.nu>
Cc: pgsql-general <pgsql-general@postgreSQL.org>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Fw: Whats happen here?
Date: Tue, 7 Dec 1999 17:57:30 +0000 (GMT)
Message-ID: <Pine.LNX.4.10.9912071753340.1210-100000@bsmlx17> (raw)
In-Reply-To: <384D1D3B.2BF89CBB@sferacarta.com>

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




view thread (4+ messages)

Message-ID: <Pine.LNX.4.10.9912071753340.1210-100000@bsmlx17>
Permalink:  ../Pine.LNX.4.10.9912071753340.1210-100000@bsmlx17/
Also on:    postgresql.org/message-id/Pine.LNX.4.10.9912071753340.1210-100000@bsmlx17

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: rison@biochemistry.ucl.ac.uk, jose@sferacarta.com, nmmm@nmmm.nu, pgsql-general@postgreSQL.org
  Subject: Re: [SQL] Fw: Whats happen here?
  In-Reply-To: <Pine.LNX.4.10.9912071753340.1210-100000@bsmlx17>
  References: <384D1D3B.2BF89CBB@sferacarta.com> <Pine.LNX.4.10.9912071753340.1210-100000@bsmlx17>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox