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