[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] Implementing DB2's "distinct" types
From:       Thomas Kellerer <spam_eater () gmx ! net>
Date:       2013-04-29 7:02:31
Message-ID: kll5rr$g4i$1 () ger ! gmane ! org
[Download RAW message or body]

Simon Riggs, 28.04.2013 21:42:
> On 21 April 2013 12:17, Thomas Kellerer <spam_eater@gmx.net> wrote:
> 
> > DB2 lets you define your own types (just as Postgres) but with the added
> > benefit that you can mark them such that they are _not_ comparable, e.g. to
> > avoid comparing "apples to oranges".
> 
> Sounds like an interesting feature we might want, but you should
> discuss it on hackers.

As I said, I don't really need this as a new feature. I was just curious.

  
> What does the SQL standard say about this?

C.J. Date calls this "domain constrained comparison".

There is the definition of "distinct type" in the standard (including a "EQUALS ONLY" \
option, but that seems to relate to ordering).

  
> Is this actually useful for anything?

I think it's just as useful as restricting the comparison between any other type \
(e.g. int vs. varchar).

> Postgres supports both domains and row types. So you can treat this as
> a row type with just one attribute.

Two different row types with just one varchar attribute are still comparable:

The following returns one row:

create type apple as (apid varchar(10));
create type orange as (orid varchar(10));

with apples (app) as (
    values (cast(row('one') as apple))
), oranges (org) as (
    values (cast(row('one') as orange))
)
select a.*
from apples a
   join oranges o on a.app = o.org;


> Look at make_row_comparison_op() in src/backend/parser/parse_expr.c

As I said: I wonder if this could be done with pure SQL, rather than creating a C \
function. But apparently this does not seem to be the case.

Cheers
Thomas




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[prev in list] [next in list] [prev in thread] [next in thread]