Re: [HACKERS] accumulated statistics

At 21:46 11/07/99 +0400, you wrote:
>I need accumulated hits statistics from my web appl. and it was looks easy
>to implement.
>
>Quick scenario:
>
>1. create table hits (msg_id int4 not null primary key, hits int4);
>2. in cgi script
> update hits set hits=hits+1 where msg_id = $msg_id;
>
>But this will not works if there are no row with msg_id,
>so I need to insert row before. I could do this in application
>but I suspect it could be done with rules.
>
>before I dig into rules programming I'd like to know if somebody
>has already have similar rule or is there another way to do this
>in postgres. I'd prefer fast solution.
>

I've done exactly this kind 'update or insert' logic using plpgsql (I presume it could be done with rules, but there may be a problem because if there is no row, how will a rule get fired?).

------------------
CREATE FUNCTION "accumulate_something" (int4,int4 ) RETURNS int4 AS '
Declare
keyval Alias For $2;
delta ALIAS For $3;
cnt int4;
Begin
Select count into cnt from summary_table where keyfield = keyval;
if Not Found then
cnt := delta;
If cnt <> 0 Then -- Don't include zero values
Insert Into summary_table (keyfield,count) values (keyval, cnt);
End If;
else
cnt := cnt + delta;
If cnt <> 0 Then
Update summary_table set count = cnt where keyfield = keyval;
Else
Delete From summary_table where keyfield = keyval;
End If;
End If;
return cnt;
End;' LANGUAGE 'plpgsql';
-----------------------

Rather than doing an update, I just call the function from SQL. You could also do it with a dummy insert into a table and use a 'before insert' trigger to prevent the insert, but cause an update on another table.

This is far less nice than it needs to be. I've sent some patches to Jan Weick for plpgsql that allow access to 'SPI_PROCESSED' which tells you how many rows were affected by the last statement. When (and if) these patches get applied, you will be able to do the following:

------------------
CREATE FUNCTION "accumulate_something" (int4,int4 ) RETURNS int4 AS '
Declare
keyval Alias For $2;
delta ALIAS For $3;
cnt int4;
rows int4;

Begin
Update summary_table set count = count + delta where keyfield = keyval;

Get Diagnostics Select PROCESSED Into rows;

If rows = 0 then
Insert Into summary_table (keyfield,count) values (keyval, delta);
End If;

End;' LANGUAGE 'plpgsql';
-----------------------

The first function has the advantage that zero values are deleted, which for my application is probably a good thing. But for web page counters, is probably unnecessary.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: +61-03-5367 7422 | _________ \
Fax: +61-03-5367 7430 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/