From: Thomas Chille <thomas@chille.de>
To: pgsql-sql@postgresql.org
Subject: distinct aggregate with complex type dont see the equality operator
Date: Mon, 29 May 2006 22:14:17 +0200
Message-ID: <cad2de1c0605291314y32e492e8gce5789eaf8a772cf@mail.gmail.com> (raw)
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!
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
In-Reply-To: <cad2de1c0605291314y32e492e8gce5789eaf8a772cf@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