Help with function optimisation
От
Ian Cass
Тема
Help with function optimisation
Дата
Msg-id
064d01c227f5$a2e06180$6602a8c0@salamander
Список
Дерево обсуждения
Help with function optimisation "Ian Cass" <ian.cass@mblox.com>
Re: Help with function optimisation Tom Lane <tgl@sss.pgh.pa.us>
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
В списке pgsql-sql по дате отправления
От: Christoph Haller
Дата: