Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id E6D894758EC for ; Wed, 10 Jul 2002 09:34:37 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g6ADYaAl016608; Wed, 10 Jul 2002 09:34:36 -0400 (EDT) To: "Ian Cass" Cc: pgsql-sql@postgresql.org Subject: Re: Help with function optimisation In-reply-to: <064d01c227f5$a2e06180$6602a8c0@salamander> References: <064d01c227f5$a2e06180$6602a8c0@salamander> Comments: In-reply-to "Ian Cass" message dated "Wed, 10 Jul 2002 10:38:36 +0100" Date: Wed, 10 Jul 2002 09:34:36 -0400 Message-ID: <16607.1026308076@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200207/137 X-Sequence-Number: 8516 "Ian Cass" 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