Thread: Help with function optimisation

Help with function optimisation

From
"Ian Cass"
Date:
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



Re: Help with function optimisation

From
Tom Lane
Date:
"Ian Cass" <ian.cass@mblox.com> writes:
> 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?

Sure.  Do the UPDATE, check how many rows updated; if none, INSERT.

Finding out how many rows were updated involves a plpgsql command
"GET DIAGNOSTICS varname = ROW_COUNT" (gotta love these Oracle-derived
syntaxes :-()
        regards, tom lane