Problems requiring a GROUP BY clause on update?

From: Joshua Moore-Oliva <josh(at)chatgris(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Problems requiring a GROUP BY clause on update?
Date: 2003-09-17 08:46:11
Message-ID: 200309170446.12105.josh@chatgris.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Childs 2003-09-17 09:02:10 Re: State of Beta 2
Previous Message Kaare Rasmussen 2003-09-17 08:45:36 Re: State of Beta 2