Message-ID: <41C6FE35.5030003@ptd.net>
Date: Mon, 20 Dec 2004 11:30:45 -0500
From: Doug Y <dylists@ptd.net>
User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: I'm stumped
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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?

Thanks