Re: I'm stumped

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 ?

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 NOT EXISTS (SELECT 1 FROM emp_dept ed WHERE ed.emp_id = e.emp_id)
AND v.div_id = 2
UNION ALL
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 emp_dept ed ON d.dept_id = ed.dept_id
INNER JOIN employees e ON e.emp_id = ed.emp_id
WHERE ed.active=true
AND v.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
>