Обсуждение: Composite type operator not unique

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;

CREATE OPERATOR =(
  PROCEDURE = bigintref_op_eq,
  LEFTARG = "BigintRef",
  RIGHTARG = "BigintRef");

Here are table definitions:

CREATE TEMP TABLE t_a(id "BigintRef");
CREATE TEMP TABLE t_b(id "BigintRef");
ALTER TABLE t_a ADD PRIMARY KEY(id);

NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"t_a_pkey" for table "t_a"
Query returned successfully with no result in 51 ms.

ALTER TABLE t_b ADD FOREIGN KEY(id) REFERENCES t_a(id);

ERROR:  operator is not unique: "BigintRef" pg_catalog.= "BigintRef"
LINE 1: ..._temp_6"."t_a" pk ON ( pk."id"::pg_catalog.record
OPERATOR(p...
                                                             ^
HINT:  Could not choose a best candidate operator. You might need to
add explicit type casts.
QUERY:  SELECT fk."id" FROM ONLY "pg_temp_6"."t_b" fk LEFT OUTER JOIN
ONLY "pg_temp_6"."t_a" pk ON ( pk."id"::pg_catalog.record
OPERATOR(pg_catalog.=) fk."id"::pg_catalog.record) WHERE pk."id" IS
NULL AND (fk."id" IS NOT NULL)

Anyone know what is wrong? I'm using PostgreSQL 9.0

Thanks

Trigve

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

On 6. Okt, 08:48 h., Trigve <trig...@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;
>
> CREATE OPERATOR =(
>   PROCEDURE = bigintref_op_eq,
>   LEFTARG = "BigintRef",
>   RIGHTARG = "BigintRef");
>
> Here are table definitions:
>
> CREATE TEMP TABLE t_a(id "BigintRef");
> CREATE TEMP TABLE t_b(id "BigintRef");
> ALTER TABLE t_a ADD PRIMARY KEY(id);
>
> NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
> "t_a_pkey" for table "t_a"
> Query returned successfully with no result in 51 ms.
>
> ALTER TABLE t_b ADD FOREIGN KEY(id) REFERENCES t_a(id);
>
> ERROR:  operator is not unique: "BigintRef" pg_catalog.= "BigintRef"
> LINE 1: ..._temp_6"."t_a" pk ON ( pk."id"::pg_catalog.record
> OPERATOR(p...
>                                                              ^
> HINT:  Could not choose a best candidate operator. You might need to
> add explicit type casts.
> QUERY:  SELECT fk."id" FROM ONLY "pg_temp_6"."t_b" fk LEFT OUTER JOIN
> ONLY "pg_temp_6"."t_a" pk ON ( pk."id"::pg_catalog.record
> OPERATOR(pg_catalog.=) fk."id"::pg_catalog.record) WHERE pk."id" IS
> NULL AND (fk."id" IS NOT NULL)
>
> Anyone know what is wrong? I'm using PostgreSQL 9.0
>
> Thanks
>
> Trigve

I forgot to mention that I've created implicit cast from BigintRef ->
bigint. This cast caused the error.

Trigve