Doug Y a écrit :
I can't figure out an efficient way to do this. Basically I had a
typical 3-tier relationship:
(Employee -> Department -> Division)
However, at some point the need to move employees arose, but instead
of changing the key in the emp table, we now have an over-ride table,
so a history can be tracked.
If I want to get the info for a particular employee, its a pretty
simple deal, however, getting all the employees for a dept or division
has become troublesome.
A very simplified schema:
divisions ( div_id, div_name );
departments ( dept_id, dept_name, div_id );
employees ( emp_id, emp_name, dept_id );
emp_dept ( emp_id, dept_id, active, changed_by, changed_when );
The original way that worked well:
SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name
FROM divisions v
INNER JOIN departments d
ON d.div_id = v.div_id
INNER JOIN employees e
ON e.dept_id = d.dept_id
WHERE v.div_id = 123;
What was initially tried:
SELECT v.div_name, COALESCE(ed.dept_id, d.dept_id), e.emp_id, e.emp_name
FROM divisions v
INNER JOIN departments d
ON d.div_id = v.div_id
INNER JOIN employees e
ON e.dept_id = d.dept_id
LEFT JOIN emp_dept ed
ON ed.emp_id = e.emp_id AND ed.active = true
WHERE v.div_id = 123;
This query is flawed, as it still always puts the employees in their
original div, but reports the new dept. Which we didn't catch as a
problem until emps were moved to depts in a different division.
I tried creating a function:
CREATE OR REPLACE FUNCTION get_empdept(int4) RETURNS int4 AS '
SELECT CASE WHEN ed.dept_id IS NOT NULL
THEN ed.dept_id
ELSE e.dept_id END
FROM employees AS e
LEFT JOIN emp_dept AS ed
ON ed.emp_id = e.emp_id AND ed.active = true
WHERE e.emp_id = $1
' LANGUAGE SQL STABLE;
And then tried:
SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name
FROM divisions v
INNER JOIN departments d
ON d.div_id = v.div_id
INNER JOIN employees e
ON get_empdept(e.emp_id) = d.dept_id
WHERE v.div_id = 123;
However since the function is not immutable (since it does a query), I
can't create an index, and the join always does a seq scan.
I also thought to create a view, but I don't believe Postgres supports
indexed views. It was always using a seq scan too.
The above examples are actually quite simplified, as several other
tables get joined along the way, I'm not sure a UNION would work or
not, how would it exclude the ones that match the dept_id in the emp
table for those emps that match on the over-ride table?
Any suggestions?
Hello,
have you an index on emp_dept on emp_id, dept_id ?
what about this ?
SELECTv.div_name, d.dept_id, e.emp_id, e.emp_name
FROMdivisions v
INNER JOIN departments d ON d.div_id = v.div_id
INNER JOIN employees e ON e.dept_id = d.dept_id
WHERENOT EXISTS (SELECT 1 FROM emp_dept ed WHERE ed.emp_id = e.emp_id)
ANDv.div_id = 2
UNION ALL
SELECTv.div_name, d.dept_id, e.emp_id, e.emp_name
FROMdivisions v
INNER JOIN departments d ON d.div_id = v.div_id
INNER JOIN emp_dept ed ON d.dept_id = ed.dept_id
INNER JOIN employees e ON e.emp_id = ed.emp_id
WHEREed.active=true
ANDv.div_id = 2
Regards,
Thomas
Thanks
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
