how to cache subtotals -- with subselect?

i'm having some trouble finding the error here -- we're trying
to cache subtotals from subset tables:

db=# select
db-#    -- person last-name:
db-#    p.lname,
db-#    -- CACHED subtotals where team involvement == 'client':
db-#    p.been_client,
db-#    -- LIVE subtotals where team involvement == 'client':
db-#    (
db(#      select count(*)
db(#      from team t join involv_v v on (t.involv_v=v.id)
db(#      where t.person=p.id and v.name='Client'
db(#    ) as ct
db-# from _person p;

  lname   | been_client | ct 
----------+-------------+----
 Trillich |           4 |  0 <== whoops
 Tharp    |           0 |  0
 Stiles   |           0 |  0
(3 rows)

here, 'trillich' has person.been_client = 4 which should instead
be 0 (that is, of the projects he's involved with, none are
as a 'client'). so...

db=#  update _person set
db-#  been_client = (
db(#     select count(*) from team t join involv_v v on (v.id=t.involv_v)
db(#     where v.name='Client' and t.person=person.id
db(#  );
NOTICE:  Adding missing FROM-clause entry in subquery for table "person"
UPDATE 3

three? that's not right. and what's up with adding a from
clause? the outer query already has a reference to _person which
the inner needs to use.

db=# select lname,been_client from person;
  lname   | been_client 
----------+-------------
 Trillich |           4
 Tharp    |           4
 Stiles   |           4
(3 rows)

ah. it used a whole 'nother instance of '_person' in the
subquery, not linking the inner query to the outer. maybe i can
alias the update?

db=#  update _person as p set
db-#  been_client = (
db(#     select count(*) from team t join involv_v v on (v.id=t.involv_v)
db(#     where v.name='Client' and t.person=p.id
db(#  );
ERROR:  parser: parse error at or near "as"

that's not it, either. clue-club welcome.


===


we're also trying to keep up-to-date with inserts and updates via
the view-and-"do instead"-rule theory:

create view team as
select
	t.id,
	t.project,
	t.person, -- foreign key into _person.id
	-- involv_v is a validation table, containing only
	-- 'vendor' and 'partner' and 'client'
	t.involv_v -- foreign key into involv_v.id
from
	_team t
;


CREATE RULE team_add AS
ON INSERT TO team
DO INSTEAD (
	INSERT INTO _team (
		project,
		person,
		involv_v
	) VALUES (
		NEW.project,
		NEW.person,
		NEW.involv_v
	);
	-- if adding a client-involvement, bump person's counter
	UPDATE _person SET
		been_client = been_client + 1
	WHERE
		id            = NEW.person
		AND
		involv_v.id   = NEW.involv_v
		AND
		involv_v.name = 'Client'
	;
);


CREATE RULE team_edit AS
ON UPDATE TO team
DO INSTEAD (
	UPDATE _team SET
		project  = NEW.project,
		person   = NEW.person,
		involv_v = NEW.involv_v
	WHERE
		id       = OLD.id
	;
	-- if OLD record was client involvement, decrement
	-- person's counter:
	UPDATE _person SET
		been_client = been_client - 1
	WHERE
		id            = OLD.person
		AND
		involv_v.id   = OLD.involv_v
		AND
		involv_v.name = 'Client'
	;
	-- if NEW record will be client involvement, increment
	-- person's counter:
	UPDATE _person SET
		been_client = been_client + 1
	WHERE
		id            = NEW.person
		AND
		involv_v.id   = NEW.involv_v
		AND
		involv_v.name = 'Client'
	;
);

the insert works, but the update doesn't -- probably endemic to
the beast (guessing that an update here isn't allowed to flow
into an insert there, at least not via a rule?)

-- 
There are 10 kinds of people:
ones that get binary, and ones that don't.
 
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
 
Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !