Re: Problems requiring a GROUP BY clause on update?

Joshua Moore-Oliva <josh(at)chatgris(dot)com> writes:
> Attribute lists.list_size_active must be GROUPed or used in an aggregate
> function

> CREATE OR REPLACE RULE items_log_insert AS ON INSERT
> TO listdb.list_items
> WHERE NEW.active = 1 DO (
> UPDATE lists SET
> list_size_active = list_size_active + COUNT( NEW.active )
> WHERE list_id = NEW.list_id;
> );

The error message isn't really helpful, perhaps, but I think the system
is quite right to squawk. What do you expect that command to do? The
COUNT() is completely meaningless because there isn't anything for it
to iterate over. (The SQL spec forbids aggregate functions in UPDATE
lists altogether, and I rather think they are right, though we've not
yet got around to installing that specific error check.)

Possibly what you want is some kind of sub-select:

UPDATE lists SET
list_size_active = list_size_active +
(SELECT COUNT(*) FROM ... WHERE ...)
WHERE list_id = NEW.list_id;

but I can't help you with what to put for "..." because you've not made
it clear what you are trying to achieve.

It's also entirely likely that you'd find an ON INSERT trigger to be
easier to work with than a rule. People frequently try to force rules
to behave like per-tuple actions, but they almost always lose the
battle. A rule is a query-level transformation, and it requires a
different mindset to use effectively.

regards, tom lane