Re: Adding columns to a view - Mailing list pgsql-general

On 28 Dec 2005, Randal L. Schwartz wrote:

>> Well, in my case the situation is further complicated by the fact that
>> adding a column to the view should be done automatically from a trigger
>> function. I wanted some kind of matrix view that had a column for every
>> row in a certain table. And whenever a new line was inserted into that
>> table the view should automatically be extended by one column.
>
> This seems wrong, with the same spidey sense tingling that triggered (ugh :)
> yesterday when I said "sending mail from the database is wrong".
> Your tables shouldn't change during the execution of your application.

You're probably right about extending the view automatically -- I have
to admit it's a pretty sick idea. But the general idea about adding new
columns to that view still seems reasonable to me.

Let me explain my current situation: I'm using Postgres for management
of a student network with some 1500 users and about 2000 hosts. We have
one table that holds information about all the users (primary key
'person_id') and another table that holds a set of possible status flags
that may be assigned to users (primary key 'status_id'). A third table
assigns those status flags to users for a certain interval:

CREATE TABLE user_has_status (
        id SERIAL PRIMARY KEY,
        person_id   integer   NOT NULL REFERENCES person(person_id),
        status_id   integer   NOT NULL REFERENCES status(status_id),
        valid_from  timestamp NOT NULL,
        valid_until timestamp NOT NULL
);

Now, if I want to get a list of users that have a certain combination of
valid status entries (e.g. all users that have paid their annual fee and
are not banned for some reason), I have to use several subselects:

SELECT person_id FROM person WHERE
    EXISTS (SELECT 1 FROM status WHERE status_id=1
            AND CURRENT_TIMESTAMP BETWEEN valid_from AND valid_until)
    AND NOT
    EXISTS (SELECT 1 FROM status WHERE status_id=2
            AND CURRENT_TIMESTAMP BETWEEN valid_from AND valid_until);

This is what I'd like to simplify: My matrix view should contain one
line for each user and one boolean column for each possible status flag.
The field content should be 'true' if the selected user has a currently
valid status entry assigned to it. The above statement could be written
a great deal shorter as:

SELECT person_id FROM person_status_matrix WHERE paid AND NOT banned;

The only problem: From time to time we need to add some new
functionality and need to introduce a new status entry. And in those
cases the matrix view needs to get a new column. That's why I'd like to
extend it without dropping all the views that might depend on it.

        Cheers,
            Ingo