how to cache subtotals -- with subselect? - Mailing list pgsql-general

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