Help with function optimisation

From: "Ian Cass" <ian(dot)cass(at)mblox(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Help with function optimisation
Date: 2002-07-10 09:38:36
Message-ID: 064d01c227f5$a2e06180$6602a8c0@salamander
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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_id AND originator =
NEW.originator;
END IF;
END IF;

RETURN NEW;
END;
' LANGUAGE 'plpgsql';

--
Ian Cass

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Andreas Schlegel 2002-07-10 11:57:52 Error with DISTINCT and AS keywords
Previous Message David BOURIAUD 2002-07-10 09:08:14 Re: query/transaction history/logs