pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Thomas Chille <thomas@chille.de>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: distinct aggregate with complex type dont see the equality operator [solved]
Date: Thu, 1 Jun 2006 16:21:58 +0200
Message-ID: <cad2de1c0606010721w546a145ap811c8acd1ca68dd4@mail.gmail.com> (raw)

using DISTINCT with complex types depends on an OPERATOR CLASS for
B-Tree-Indexes i found out. the error msg 'could not identify an
equality operator for type' was confusing.

i post this complete example. maybe someone else is running in this problem too.

regards,
thomas!



CREATE TYPE named_value AS (
    value_name text,
    value numeric
);

CREATE OR REPLACE FUNCTION named_value_lt(named_value, named_value)
RETURNS boolean AS $f$
    SELECT $1.value_name < $2.value_name;
$f$ LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION named_value_lt_eq(named_value, named_value)
RETURNS boolean AS $f$
    SELECT $1.value_name <= $2.value_name;
$f$ LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION named_value_eq(named_value, named_value)
RETURNS boolean AS $f$
    SELECT $1.value_name = $2.value_name;
$f$ LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION named_value_gt_eq(named_value, named_value)
RETURNS boolean AS $f$
    SELECT $1.value_name >= $2.value_name;
$f$ LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION named_value_gt(named_value, named_value)
RETURNS boolean AS $f$
    SELECT $1.value_name > $2.value_name;
$f$ LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION named_value_cmp(named_value, named_value)
RETURNS integer AS $f$
    SELECT CASE
        WHEN $1.value_name < $2.value_name THEN -1
        WHEN $1.value_name = $2.value_name THEN 0
        ELSE 1
    END;
$f$ LANGUAGE sql IMMUTABLE STRICT;

CREATE OPERATOR < (
    PROCEDURE = named_value_lt,
    LEFTARG = named_value,
    RIGHTARG = named_value,
    COMMUTATOR = >
);

CREATE OPERATOR <= (
    PROCEDURE = named_value_lt_eq,
    LEFTARG = named_value,
    RIGHTARG = named_value,
    COMMUTATOR = >=
);

CREATE OPERATOR = (
    PROCEDURE = named_value_eq,
    LEFTARG = named_value,
    RIGHTARG = named_value,
    COMMUTATOR = =
);

CREATE OPERATOR >= (
    PROCEDURE = named_value_gt_eq,
    LEFTARG = named_value,
    RIGHTARG = named_value,
    COMMUTATOR = <=
);

CREATE OPERATOR > (
    PROCEDURE = named_value_gt,
    LEFTARG = named_value,
    RIGHTARG = named_value,
    COMMUTATOR = <
);

CREATE OPERATOR CLASS named_value_ops
    DEFAULT FOR TYPE named_value USING btree AS
        OPERATOR    1    < ,
        OPERATOR    2    <= ,
        OPERATOR    3    = ,
        OPERATOR    4    >= ,
        OPERATOR    5    > ,
        FUNCTION    1    named_value_cmp(named_value, named_value);


CREATE OR REPLACE FUNCTION sum_final(named_value)
RETURNS numeric AS $f$
    SELECT $1.value;
$f$ LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION sum_accum(named_value, named_value)
RETURNS named_value AS $f$
    SELECT ROW('', $1.value + $2.value)::named_value;
$f$ LANGUAGE sql IMMUTABLE STRICT;

CREATE AGGREGATE sum(
     BASETYPE = named_value,
     SFUNC = sum_accum,
     STYPE = named_value,
     FINALFUNC = sum_final,
     INITCOND = "('',0)"
);
-- result is FALSE
SELECT ROW('foo', 5)::named_value = ROW('bar', 5)::named_value;

-- result is TRUE
SELECT ROW('foo', 5)::named_value = ROW('foo', 5)::named_value;

-- result is TRUE
SELECT ROW('foo', 4)::named_value = ROW('foo', 5)::named_value;

-- works for me
SELECT sum(ROW(name, wert)::named_value) FROM table1;

-- now works for me too
SELECT DISTINCT ROW(wert, name)::named_value FROM table1;
SELECT sum(DISTINCT ROW(name, wert)::named_value) FROM table1;

view thread (2+ messages)

Message-ID: <cad2de1c0606010721w546a145ap811c8acd1ca68dd4@mail.gmail.com>
Permalink:  ../cad2de1c0606010721w546a145ap811c8acd1ca68dd4@mail.gmail.com/
Also on:    postgresql.org/message-id/cad2de1c0606010721w546a145ap811c8acd1ca68dd4@mail.gmail.com

copy link · copy postgr.es

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 using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: thomas@chille.de
  Subject: Re: distinct aggregate with complex type dont see the equality operator [solved]
  In-Reply-To: <cad2de1c0606010721w546a145ap811c8acd1ca68dd4@mail.gmail.com>

* 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