pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with function optimisation
2+ messages / 2 participants
[nested] [flat]
* Help with function optimisation
@ 2002-07-10 09:38 Ian Cass <ian.cass@mblox.com>
2002-07-10 13:34 ` Re: Help with function optimisation Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Ian Cass @ 2002-07-10 09:38 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Help with function optimisation
2002-07-10 09:38 Help with function optimisation Ian Cass <ian.cass@mblox.com>
@ 2002-07-10 13:34 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2002-07-10 13:34 UTC (permalink / raw)
To: Ian Cass <ian.cass@mblox.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-07-10 13:34 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-10 09:38 Help with function optimisation Ian Cass <ian.cass@mblox.com>
2002-07-10 13:34 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox