Re: Trigger function failure

On 7 March 2016 at 07:43, Michael Rowan <michael(dot)rowan3(at)gmail(dot)com> wrote:

> I have a trigger function problem that threatens to cause my early
> demise. Please someone help an absolute novice.
>
> I have two tables, invoice and invline, with the usual functions.
>
> Table invoice has a column for the total of debit lines, and a column for
> the total of credit (payment) lines. The function is triggered by any
> change in inv.gross
>
> The ERROR occurs if there are no invline found by either summing part,
> where we set dr_total or cr_total.
> If I change "sum" to "count" it works as expected, returning 0.00
>
>
> --THE FUNCTION
> UPDATE invoice SET
> invoice.cr_total = (
> SELECT
> sum(invline.gross)
> FROM invline
> WHERE invline.type > 4 AND invline.invoice_id = 200003
> )
> ,
> invoice.dr_total = (
> SELECT
> sum(invline.gross)
> FROM invline
> WHERE invline.type <5 AND invline.invoice_id = 200003
> )
> WHERE invoice.id = 200003;
>
> --TEST
> select invoice.dr_total, invoice.cr_total from invoice where invoice.id
> =200003
>
>
> ---------------------------------------------------------------------------------------------
>
> ERROR: null value in column "po_cr_total" violates not-null constraint
>
>
> Anyone?
>
>
> Mike
>
There is a difference between the behaviour of sum and count. Count returns
the number of found records(0 in this case) while sum returns the sum of
the given expression from the found records. And anything plus null is
always null in Postgres.
However you can use the coalesce function which returns its first non-null
argument to ensure a non null result.

SELECT
coalece(sum(invline.gross), 0)
FROM invline
WHERE invline.type > 4 AND invline.invoice_id = 200003

or

invoice.cr_total = coalesce((SELECT
sum(invline.gross)
FROM invline
WHERE invline.type > 4 AND invline.invoice_id = 200003),0)

Regards,
Sándor