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/ !
