Help with function optimisation
От | Ian Cass |
---|---|
Тема | Help with function optimisation |
Дата | |
Msg-id | 064d01c227f5$a2e06180$6602a8c0@salamander обсуждение исходный текст |
Ответы |
Re: Help with function optimisation
|
Список | 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 по дате отправления: