Re: [HACKERS] accumulated statistics - Mailing list pgsql-sql

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
maybe 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;
Ifcnt <> 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
wherekeyfield = 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
anduse 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.
Butfor 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   |/