pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedaccumulated statistics
2+ messages / 2 participants
[nested] [flat]
* accumulated statistics
@ 1999-07-11 17:46 Oleg Bartunov <oleg@sai.msu.su>
1999-07-12 02:36 ` Re: [HACKERS] accumulated statistics Philip Warner <pjw@rhyme.com.au>
0 siblings, 1 reply; 2+ messages in thread
From: Oleg Bartunov @ 1999-07-11 17:46 UTC (permalink / raw)
To: pgsql-hackers@postgreSQL.org; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [HACKERS] accumulated statistics
1999-07-11 17:46 accumulated statistics Oleg Bartunov <oleg@sai.msu.su>
@ 1999-07-12 02:36 ` Philip Warner <pjw@rhyme.com.au>
0 siblings, 0 replies; 2+ messages in thread
From: Philip Warner @ 1999-07-12 02:36 UTC (permalink / raw)
To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: pgsql-sql; wieck@debis.com
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 |/
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1999-07-12 02:36 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-11 17:46 accumulated statistics Oleg Bartunov <oleg@sai.msu.su>
1999-07-12 02:36 ` Philip Warner <pjw@rhyme.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox