Re: Adding columns to a view

On Wed, Dec 28, 2005 at 07:29:28PM +0100, Ingo van Lil wrote:
> 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;

Another alternative would be to create a check_status function that
did the lookup for you. If done correctly (as in using SQL as the
language and setting it to STABLE), the optimizer should inline the
fuction, giving you the same performance as the 1st query but without
all the typing (btw, isn't that first query missing person_id as part of
the WHERE clause in the EXISTS subqueries?)
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461