Problems requiring a GROUP BY clause on update?

[Joshua Moore-Oliva]

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_activ e = COUNT(NEW.activ e)

PROPERLY sets the value to the number of new items.

However,

list_size_activ e = list_size_activ e + COUNT(NEW.activ e)

Gives and error about needing to group the column and

list_size_activ e = list_size_activ e + ( SELECT COUNT(NEW.activ e) )

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_inser t AS ON INSERT
TO listdb.list_ite ms
WHERE NEW.active = 1 DO (
UPDATE lists SET
list_size_activ e = list_size_activ e + 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_inser t AS ON INSERT
TO listdb.list_ite ms
WHERE NEW.active = 1 DO (
UPDATE lists SET
list_size_activ e = 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_activ e to 1 no matter how many rows are in NEW.

CREATE OR REPLACE RULE items_log_inser t AS ON INSERT
TO listdb.list_ite ms
WHERE NEW.active = 1 DO (
UPDATE lists SET
list_size_activ e = list_size_activ e + ( SELECT COUNT( NEW.active ) )
WHERE list_id = NEW.list_id;
);

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

Joshua Moore-Oliva <jo**@chatgris. com> writes:

Attribute lists.list_size _active must be GROUPed or used in an aggregate
function CREATE OR REPLACE RULE items_log_inser t AS ON INSERT
TO listdb.list_ite ms
WHERE NEW.active = 1 DO (
UPDATE lists SET
list_size_activ e = list_size_activ e + 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_activ e = list_size_activ e +
(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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Joshua Moore-Oliva]

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 <jo**@chatgris. com> writes:

Attribute lists.list_size _active must be GROUPed or used in an aggregate
function

CREATE OR REPLACE RULE items_log_inser t AS ON INSERT
TO listdb.list_ite ms
WHERE NEW.active = 1 DO (
UPDATE lists SET
list_size_activ e = list_size_activ e + 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_activ e = list_size_activ e +
(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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)