Re: distinct aggregate with complex type dont see the equality operator [solved] - Mailing list pgsql-sql

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_nameTHEN 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;