DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:sender:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition:x-google-sender-auth;
	b=Dgpg/zXBiJv7Nptxqs/seq5FKSEmrbLcl7j9Ft9O9wFsbZXytvsD8Y7EuVEt2SSaejzjXLPHcP9TR8PQR0cAGr65Tu9bNKjrbU5oprrBGycF+gqGozMEywHYf/sy/hn0572GqnSiLBX7fcf0RpUi+9g79hlA/xCXVLRtvg0OlkU=
Message-ID: <cad2de1c0605291314y32e492e8gce5789eaf8a772cf@mail.gmail.com>
Date: Mon, 29 May 2006 22:14:17 +0200
From: "Thomas Chille" <thomas@chille.de>
To: pgsql-sql@postgresql.org
Subject: distinct aggregate with complex type dont see the equality operator
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Dear List,

i want to built an aggregate function wich should summing up values in
a distinct manner:

dsum(DISTINCT ROW(value, distinction)).

I implemented all things i thought i need but it dont work. i get
always an 'could not identify an equality operator for type
dsum_type'-error nevertheless the operator will be identified outside
the aggregate.

Here is the code for the equality operator:

CREATE OR REPLACE FUNCTION dsum_type_greater(dsum_type, dsum_type)
RETURNS boolean AS $f$
    SELECT $1.distinction > $2.distinction;
$f$ LANGUAGE sql IMMUTABLE STRICT;

CREATE OPERATOR = (
    PROCEDURE = dsum_type_equality,
    LEFTARG = dsum_type,
    RIGHTARG = dsum_type,
    COMMUTATOR = =
);

And this happens:

-- result is FALSE
SELECT ROW(5, 'foo')::dsum_type = ROW(5, 'bar')::dsum_type;

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

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

-- works for me
SELECT dsum(ROW(wert, name)::dsum_type) FROM table1;

-- ERROR: could not identify an equality operator for type dsum_type
SELECT dsum(DISTINCT ROW(wert, name)::dsum_type) FROM table1;

i added the less then and greater then ops too, but it still wont work.

What i am doing wrong or can i achieve the wished result without
usinge a comlex type?

regards,
Thomas!