Message-ID: <064d01c227f5$a2e06180$6602a8c0@salamander>
From: "Ian Cass" <ian.cass@mblox.com>
To: pgsql-sql@postgresql.org
Subject: Help with function optimisation
Date: Wed, 10 Jul 2002 10:38:36 +0100
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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