Re: Trigger function failure

Hi Michael,

From http://www.postgresql.org/docs/current/static/functions-aggregate.html:

It should be noted that except for count, these functions return a null
value when no rows are selected.* In particular, sum of no rows returns
null, not zero as one might expect*, and array_agg returns null rather than
an empty array when there are no input rows.

(I’m not sure, but I think that’s actually required behavior from the SQL
specification.)

This explains why your query works with COUNT(): that function *is*
guaranteed to return 0 when run over an empty set.

You can fix that by wrapping COALESCE() call around your subselect: write

COALESCE((SELECT …), 0)

instead of

(SELECT …)

[]s, Fábio.

On Mon, Mar 7, 2016 at 3:43 PM, 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
>

--
Fábio Dias Moreira