Re: Setting WHERE on a VIEW with aggregate function.

>> > I have a view to generate a list of instructors and a count of their
>> > future classes.

>> > select * from instructor_counts where class_time > now();
>> >
>> > But class_time is not part of the VIEW so that's not valid.
>>
>> No problem, just make it a part of the view.

"No problem", I said ... famous last words.

> select * from instructor_counts order by class_count desc;
>
> -- Returns:
>
> person_id | first_name | class_count
> -----------+------------+-------------
> 1 | Joe | 2
> 2 | Mary | 1
> 3 | Bob | 1
> 4 | Cindy | 1
> (4 rows)
>
> My GOAL above is to be able to add a WHERE class_time > $some_time.

Armed with your table ddl and sample data I can see the problem more clearly.

Unfortunately class_time cannot be a column in the view output. For example, look at the "Joe" line above ... if he teaches two classes which "class_time" would it show?

Since class_time can't be a column in the view output it can't be used in a WHERE clause.

So it would appear to me that you won't able to meet your goal by simply using a view.

However, there are other solutions you can use which may or may not be appropriate. I can think of three at the moment.

#1: Function Solution:
================
To use functions you may first need to run this at command line:

createlang plpgsql electric

Then create a function that you can pass in a date to:

CREATE FUNCTION getclasscount("timestamp") RETURNS "refcursor" AS '
DECLARE curs refcursor;
BEGIN
OPEN curs FOR
SELECT * FROM
(SELECT
person, count(class) AS class_count
FROM instructors
INNER JOIN class
ON class.id = instructors.person
WHERE class.class_time > $1
GROUP BY person
) classcount

INNER JOIN

(SELECT
person.id AS person_id,
first_name FROM person
) personinfo

ON personinfo.person_id = classcount.person
RETURN curs;
END;
' LANGUAGE 'plpgsql';

This would be the best solution if you are in control of the application source code. In Java, for example, it's relatively simple to call this function and return the result as a result set. If you're working in Java I'd be glad to show you same sample code.

If you really do need a view for some reason, then this wouldn't work.

#2: Simplify the Select Criteria Solution:
=============================

A slightly less flexible approach, but one that may be workable, would be to add two boolean columns (with default value of '0') to the class table: "completed" and "current". Then once a semester you run a simple query that updates them. Something like:

UPDATE class SET current = '1' where class_time = '2005-09-01';
UPDATE class SET completed = '1' where class_time < '2005-09-01';

Then the view would be:

CREATE VIEW vclasscount AS
SELECT * FROM
(SELECT
person, count(class) AS class_count
FROM instructors
INNER JOIN
(SELECT id FROM class
WHERE class.completed = '0'
AND class.current = '0') futureclasses
ON futureclasses.id = instructors.class
GROUP BY person
) classcount

INNER JOIN

(SELECT
person.id AS person_id,
first_name FROM person
) personinfo

ON personinfo.person_id = classcount.person

This would be a nice simple solution, but may not be flexible enough in that you can't specify an ad-hoc date or date-range ... you would only see future class count.

3: Use a Temp Table
===============
Again, if you have control of application logic, you could:

1) SELECT * INTO futureclasses FROM class where class_time > ?

2) Then make the view against futureclasses rather than classes.

Good luck!

-Roger

> Bill Moseley