Thread: Problems requiring a GROUP BY clause on update?

I have a query that is asking me to GROUP a column,  yet when I GROUP it it
causes an error near GROUP.


What is very strange about the following query is that the line

list_size_active = COUNT(NEW.active)

PROPERLY sets the value to the number of new items.

However,

list_size_active = list_size_active + COUNT(NEW.active)

Gives and error about needing to group the column and

list_size_active = list_size_active + ( SELECT COUNT(NEW.active) )

Only increments the value by one while the first only assigning statement
actually assigns it to the number of new items.

Here is what I have tried so far with varying results.  I am totally out of
ideas beyond this :(

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;
);




This function sets the value to the appropriate number, but fails to increment
it as needed proving that the number of items is attainable.

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 = COUNT( NEW.active )
      WHERE list_id = NEW.list_id;
);




This function does not shoot any errors off when I create the RULE.

However, it sets list_size_active to 1 no matter how many rows are in NEW.

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 + ( SELECT COUNT( NEW.active ) )
      WHERE list_id = NEW.list_id;
);

Joshua Moore-Oliva <josh@chatgris.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

I am attempting to find out how many rows a query operation affected, then
update a separate row to keep count, because running a SELECT COUNT(id) FROM
lists is far too slow when there are millions of records and the number if
required for a user interface.

So far my only success has only come from making functions for each operation,
and using GET DIAGNOSTICS num_affected := ROW_COUNT;

I don't want triggers to update one at a time because I at times insert
millions of rows, and don't think that millions of update statements is too
healthy :).

Thanks, Josh.


On September 17, 2003 11:23 am, Tom Lane wrote:
> Joshua Moore-Oliva <josh@chatgris.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