[SQL] difficult JOIN

[Thomas Chille]

Hi,

i have the following SQL-Problem:

We are using 2 tables. The first, called plan, is holding planned
working times for employees per
tour:

plan.id_tour
plan.id_employee
plan.begin_time
plan.end_time

The second table 'work' stores the actual worked times for employees per tour:

work.id_tour
work.id_employee
work.begin_time
work.end_time

Employees can be multiple times assigned to one tour. One record will
be created for every
assignment. They can also work multiple times in one tour.

Now i wanna merge this infos into one report. I wanna join the first
plan entry for one employee in
one tour with the first work entry for one employee in one tour and so on.

How can i obtain that? A simply USING(id_tour, id_employee) -JOIN will not doit.

Thanks for any hints,
Thomas

---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] distinct aggregate with complex type dont see the equality operator

[Thomas Chille]

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!

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] distinct aggregate with complex type dont see the equality operator [solved]

[Thomas Chille]

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
   OPERATOR1< ,
   OPERATOR2<= ,
   OPERATOR3= ,
   OPERATOR4>= ,
   OPERATOR5> ,
   FUNCTION1named_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;

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly