Re: Composite type operator not unique - Mailing list pgsql-general

From Merlin Moncure
Subject Re: Composite type operator not unique
Date
Msg-id AANLkTikpFzdFUO_Vh4pzg1gMHzNFkmw-OJ8HfjezG4dG@mail.gmail.com
Whole thread Raw
In response to Composite type operator not unique  (Trigve <trigves@gmail.com>)
List pgsql-general
On Wed, Oct 6, 2010 at 2:48 AM, Trigve <trigves@gmail.com> wrote:
> Hi,
> I'm trying to make a custom composite type and use it as a PK and FK.
> When adding FK to table I've got trhis error: operator is not unique:
> "BigintRef" pg_catalog.= "BigintRef" (see below). Here is my type
> definition with operator:
>
> CREATE TYPE "BigintRef" AS
>   ("Value" bigint,
>    "Null" boolean);
>
> CREATE OR REPLACE FUNCTION bigintref_op_eq("BigintRef", "BigintRef")
>  RETURNS boolean AS
> 'SELECT ($1."Null" = TRUE AND $2."Null" = TRUE) OR ($1."Null" = FALSe
> AND $2."Null" = FALSE AND $1."Value" = $2."Value")'
>  LANGUAGE sql IMMUTABLE
>  COST 100;

you shouldn't have to do this -- as of 8.4 record types can be
directly used in comparisons w/o going custom operator route and I
would highly advise against redefining the = operator to use
non-standard behavior.

postgres=# create table foo(a int, b bool);
CREATE TABLE
Time: 11.433 ms
postgres=# select (1, true)::foo = (2,false)::foo;
 ?column?
----------
 f
(1 row)

Time: 1.424 ms

merlin

pgsql-general by date:

Previous
From: Magnus Hagander
Date:
Subject: Re: querying the version of libpq
Next
From: Jutta Buschbom
Date:
Subject: Re: Compilation of optional packages