Re: Constraint on an aggregate? (need help writing trigger,

Isak Hansen wrote:

Hello Isak! I was speaking to you about this on comp.databases, glad to
see you here on the Postgres group.

What you want to do is easy enough in the single case, but can get
complicated if you do a lot of it.

<shameless plug>
We have a framework that is freely available that does exactly what you
are trying to do, and works against PostgreSQL and is written in PHP.
It writes all of the triggers for you to save the hassle and prevent
mistakes. We even have on our website an example of the kind of
constraint you are doing:

http://docs.secdat.com/index.php?gp_page=x_docview&gppn=Customer+Credit+Limit
</shamelss plug>

If you wish to code this by hand, here is what you must do:

1) Add column "amount" to table A
2) Add insert, update, and delete triggers to B that increase and
decrease the value of A.amount
3) Add an update trigger to A (insert and delete not necessary) that
enforces your constraint, or just do it as a check constraint (i
personally prefer triggers)

BTW, is table A supposed to be a GL batch summary table or something?
Why must it always be zero? If it is a GL batch table, can it be out of
balance while people are actually entering the data? Should the
constraint only be enforced when the batch is closed?

>
> create table a (
> id serial primary key,
> );
> create table b (
> id serial primary key,
> a_id int4 references a (id),
> amount decimal(16, 2)
> );
>
> and would like a constraint to guarantee that "sum(b.amount) = 0 group
> by b.a_id".
>
>
> From my testing so far, and this thread:
> <http://groups.google.com/group/comp.databases/browse_thread/thread/c2ce2d61eecf5c6c/1e0fa71282aea7d8#1e0fa71282aea7d8>,
>
> i think a trigger is the way to go.
>
> Problem is, i have no idea where to go from here. Getting the model
> nearly to to 3NF and writing some simple queries is about the extent
> of my db-related skillset..
>
> Anyone feel the calling..? ;)
>
>
> Also, how would this kind of check affect performance? Table 'b' is
> our ledger table, busiest one in the app i assume, while 'a' groups
> related transactions and holds common info. We inherited the term
> voucher for 'a', anyone know if that is(n't) appropriate?
>
> If someone are interested, the actual tables are here:
> a: http://trac.lodo.no/wiki/vouchers
> b: http://trac.lodo.no/wiki/voucher_lines
>
>
> Any feedback appreciated,
> Isak
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match