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

Hi,

I didn't bother analysing this too deeply, so keep that in mind when you 
read my reply ;-)

However the point that set off alarm bells for me was this statement "we now 
have an over-ride table".

It seems to me that you shouldn't be over-riding anything, and the emp_dept 
table should just be history -  ie "emp_dept_his", and it wouldn't need the 
"active" column.

If you need history then you could use a trigger or rule to insert the 
history record whenever the emloyee changes department.

The key question here is "Why wouldn't you change the key in the emp table 
when that key represents the employee's department?". It seems like a 
handbook case to me but maybe I missed something.

regards
Iain

>> 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