pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Philip Warner <pjw@rhyme.com.au>
To: Oleg Bartunov <oleg@sai.msu.su>
Cc: pgsql-sql@postgreSQL.org
Cc: wieck@debis.com
Subject: Re: [HACKERS] accumulated statistics
Date: Mon, 12 Jul 1999 12:36:24 +1000
Message-ID: <3.0.5.32.19990712123624.0094a100@mail.rhyme.com.au> (raw)
In-Reply-To: <Pine.GSO.3.96.SK.990711212748.2043b-100000@ra>

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   |/



view thread (2+ messages)

Message-ID: <3.0.5.32.19990712123624.0094a100@mail.rhyme.com.au>
Permalink:  ../3.0.5.32.19990712123624.0094a100@mail.rhyme.com.au/
Also on:    postgresql.org/message-id/3.0.5.32.19990712123624.0094a100@mail.rhyme.com.au

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: pjw@rhyme.com.au, oleg@sai.msu.su, wieck@debis.com
  Subject: Re: [HACKERS] accumulated statistics
  In-Reply-To: <3.0.5.32.19990712123624.0094a100@mail.rhyme.com.au>
  References: <Pine.GSO.3.96.SK.990711212748.2043b-100000@ra> <3.0.5.32.19990712123624.0094a100@mail.rhyme.com.au>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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