Re: Trigger function failure

Поиск
Список
Период
Сортировка
От Fábio Moreira
Тема Re: Trigger function failure
Дата
Msg-id CANQddpN5NR-bVzbPQ38mePh1YQeWQCLjeqzZ6FXj=qZn5LtHWQ@mail.gmail.com
обсуждение исходный текст
Ответ на Trigger function failure  (Michael Rowan <michael.rowan3@gmail.com>)
Список pgsql-novice
Дерево обсуждения
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.rowan3@gmail.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

В списке pgsql-novice по дате отправления: