Hack 60. Create a List of Personalized Parameters

Instead of creating a single value per variable for each user, it may be necessary to associate multiple values with each user for a single variable. Suppose each member of a staff is responsible for multiple students. Each staff member can access a personalized view that they can use as a basis for their own queries.

A member of staff (MoS), William, is responsible for students 84001001, 84001002, and 84001003. Another MoS, Priya, is responsible for students 84001004 and 84001005. Each MoS has their own database system username: William and Priya. You can encode each user's students in the rules table, which has an entry for every student, as shown in Table 8-2.

Table 8-2. The rules table

MoS	Student
William	84001001
William	84001002
William	84001003
Priya	84001004
Priya	84001005

You can create a personalized view of this table by referencing the current user's login name. The myStudents view is personalizedwhen William runs it, it shows just his students; when Priya runs it, she will see just her students:

CREATE VIEW myStudents AS
 SELECT student FROM rules WHERE mos=CURRENT_USER;

Some database systems vary in terms of how to access the CURRENT_USER information, and how to insert data into a view. See "Define Personalized Parameters" [Hack #59].

You can set up the rules table so that each user can insert into myStudent without reference to the current username:

CREATE TABLE rules
(mos VARCHAR(20) DEFAULT CURRENT_USER
,student CHAR(8) PRIMARY KEY
);

Now Priya can issue the INSERT statement:

INSERT INTO myStudents(student) VALUES ('05001006');

Her username will be put into the mos column from the default value.

The beauty of this method is that you can create many complex and useful queries that are based on a view such as myStudent. Anyone can use these queries, without alterationeach user will see results limited to only those students she is interested in.

Updating or inserting via a view in PostgreSQL 8.2 or below is not supported. Hopefully this will be remedied in a later version of PostgreSQL.