Thread: PL/pgSQL Question

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..

I looked in the documentation but the examples only talk about rearranging
values within the tuple that fired the trigger which called the function
(wow, a mouth full).

Thanks!

-Mitch

Er, I'm pretty sure I found what I was looking for, sorry to waste
everyone's time.. I looked right past half the documentation!

-Mitch

----- Original Message -----
From: "Mitch Vincent" <mitch@venux.net>
To: <pgsql-general@postgresql.org>
Sent: Monday, January 22, 2001 6:56 PM
Subject: PL/pgSQL Question


> 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..
>
> I looked in the documentation but the examples only talk about rearranging
> values within the tuple that fired the trigger which called the function
> (wow, a mouth full).
>
> Thanks!
>
> -Mitch
>
>
>

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