serial data type usage - Mailing list pgsql-general

I have a table where I would like the primary key to be generated during
the insert.

Here is a simplified example:


CREATE TABLE employee_type
{
    tname varchar(10) PRIMARY KEY,
    id_prefix char(1) ;
    ...
}

tname      | id_prefix
--------------+----------
worker     | W
manager  | M
executive | E

CREATE TABLE employee {
    id varchar(10) PRIMARY KEY,
    type varchar(10) REFERENCES employee_type
    ...
}

When an employee of type 'worker' is inserted the id generated will have
a prefix "W" followed by a 6-digit number. W000001, W000002 ..
When the employee type is 'manager' the employee id is M000001, M000002
...
When the employee type is 'executive' the employee id is E000001,
E000002 ...

The sequences for each employee type are separate.

CREATE SEQUENCE worker_seqnum MINVALUE 1 MAXVALUE 999999 ;
CREATE SEQUENCE manger_seqnum MINVALUE 1 MAXVALUE 999999 ;
CREATE SEQUENCE executive_seqnum MINVALUE 1 MAXVALUE 999999 ;


I have thought about using the serial data type for the employee.id but
I also want to automate the prepending of the { W, M, E } prefix.

Any suggestions?