Re: I'm stumped - Mailing list pgsql-sql

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
>