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.

	Regards,

		Oleg


_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83



Sun, 11 Jul 1999 21:46:04 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
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   |/

Mon, 12 Jul 1999 12:36:24 +1000
Philip Warner <pjw@rhyme.com.au>
Re: [HACKERS] accumulated statistics