Re: Setting WHERE on a VIEW with aggregate function. - Mailing list pgsql-general

>> > 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
teachestwo 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
relativelysimple to call this function and return the result as a result set. If you're working in Java I'd be glad to
showyou 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
valueof '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