From: | Emmanuel Charpentier <charpent(at)bacbuc(dot)dyndns(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: PL/pgSQL Question |
Date: | 2001-01-23 08:11:37 |
Message-ID: | 3A6D3CB9.867BBB@bacbuc.dyndns.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Mitch Vincent wrote:
>
> I haven't used PL/pgSQL very much but it looks like a good language in which
> to make some simple functions for this application I'm writing..
>
> Is it possible (with PL/pgSQL) to access other records in other tables than
> the tuple that pulled the trigger (and called the function)?
>
> Say this (pseudo code):
>
> When a record is added to the invoice_payments table have a trigger fire and
> call this function :
>
> total_invoice()
>
> exec_sql -> UPDATE invoices SET total = total + <amount in amount_field in
> the tuple> WHERE invoice_id=<value in the invoice ID of this tuple>
>
> That's basically all I'd have to do.. There are a lot of these things that
> could be handled by the backend and would make my life much easier..
Much simpler (but I might be misunderstanding what you're aiming at ...)
CREATE VIEW invoices as (SELECT invoice_id, SUM(amount) group by
invoice_id FROM invoice_payments) as t1 join (<whatever you need from
other tables>) as t2 on t1.invoice_id=t2.invoice_id;
That way, you don't have to worry about maintaining consistency between
invoice_payments and invoices. Which is all the point of having a
Codd-conformant RDBMS ! No hassles with updates and/or deletions in
invoice_payments ...
Emmanuel Charpentier
--
Emmanuel Charpentier
From | Date | Subject | |
---|---|---|---|
Next Message | leif | 2001-01-23 08:58:26 | Re: ACS Web Server & PostgreSQL |
Previous Message | Robert B. Easter | 2001-01-23 07:07:55 | Re: Data entry screen building utilities |