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