Help with function optimisation - Mailing list pgsql-sql

From Ian Cass
Subject Help with function optimisation
Date
Msg-id 064d01c227f5$a2e06180$6602a8c0@salamander
Whole thread Raw
Responses Re: Help with function optimisation
List pgsql-sql
Hi,

I've got a function that I run as a trigger to update a summary table on
insert to the main table. As you can see below, it does a select & an INSERT
if not found, or an UPDATE if found. This currently works OK, but I'd like
to improve performance by removing the SELECT & attempting an UPDATE. If it
fails, I'd like to INSERT instead. Can this be done? If so, what's the
syntax?

CREATE FUNCTION update_summary ()
RETURNS opaque AS '       DECLARE
       result TEXT;

BEGIN
       IF NEW.user_name NOT LIKE ''U%'' THEN               SELECT into result originator FROM summary
   WHERE date = date_trunc(''hour'', NEW.logtime)                       AND client_id = NEW.client_id AND originator =
 
NEW.originator;
               IF NOT FOUND THEN                       INSERT INTO summary (date, client_id, originator,
status, total)                       values (date_trunc(''hour'', NEW.logtime),
NEW.client_id, NEW.originator, NEW.status, ''1'');               ELSE                       UPDATE summary SET total =
total+ 1                       WHERE date = date_trunc(''hour'', NEW.logtime)                       AND client_id =
NEW.client_idAND originator =
 
NEW.originator;               END IF;       END IF;
   RETURN NEW; END;
' LANGUAGE 'plpgsql';

--
Ian Cass



pgsql-sql by date:

Previous
From: David BOURIAUD
Date:
Subject: Re: query/transaction history/logs
Next
From: Christoph Haller
Date:
Subject: Re: Problem on PostgreSQL (error code, store procedures)