From: "codeWarrior" <gpatnude@hotmail.com>
Subject: Re: automatic update or insert
Date: Tue, 25 Oct 2005 08:26:16 -0700
Organization: Hub.Org Networking Services
Lines: 79
Message-ID: <djlinr$2mu1$1@news.hub.org>
References: <1129790184.351579.302550@g47g2000cwa.googlegroups.com>
To: pgsql-sql@postgresql.org

The following trigger procedure works for me.... you'd need to adjust this 
to manipulate YOUR table schema:

DROP FUNCTION dmc_comp_plan_duplicates() CASCADE;
CREATE OR REPLACE FUNCTION dmc_comp_plan_duplicates() RETURNS "trigger" AS
$BODY$

 DECLARE did integer;

     BEGIN

  SELECT COALESCE(id, 0) AS id FROM dmc_compensation_plan_quota
  WHERE dmc_compensation_plan = NEW.dmc_compensation_plan
  AND dmc_quota_item = NEW.dmc_quota_item
  INTO did;

  RAISE NOTICE 'DID: %', did;

  IF ((did = 0) OR (did IS NULL)) THEN

   RAISE NOTICE 'INSERT: DID: %', did;
   -- INSERT INTO dmc_compensation_plan_quota (dmc_compensation_plan, 
dmc_quota_item) VALUES (NEW.dmc_compensation_plan, NEW.dmc_quota_item);
   RETURN NEW;

  ELSE

   RAISE WARNING 'UPDATE: DID: %', did;
   UPDATE dmc_compensation_plan_quota SET active_flag = TRUE WHERE id = did;
   RETURN NULL;

  END IF;

  -- DEFAULT = DO NOTHING...
  RETURN NULL;

     END;

$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION dmc_comp_plan_duplicates() OWNER TO datamosaics;
CREATE TRIGGER dmc_comp_plan_duplicates BEFORE INSERT ON 
dmc_compensation_plan_quota FOR EACH ROW EXECUTE PROCEDURE 
dmc_comp_plan_duplicates();


"tobbe" <tobbe@tripnet.se> wrote in message 
news:1129790184.351579.302550@g47g2000cwa.googlegroups.com...
> Hi.
>
> I have a little problem.
>
> In a system of mine i need to insert records into table [tbStat], and
> if the records exist i need to update them instead and increase a
> column [cQuantity] for every update.
>
> I.e. the first insert sets cQuantity to 1, and for every other run
> cQuantity is increased.
>
> Currently i have implemented this as a stored procedure in the plpgsql
> language. This means that in my stored procedure i first do a select to
> find out if the row exists or not, then i do a insert or update
> depending if the row existed.
>
> Unfortunately, stored procedures seems awfully slow. And i need the
> application to go faster.
>
> One solution could be to implement the stored procedure in my program
> instead. I think that this will be atleast 50% faster than my stored
> procedure, so that would be ok.
>
> However, this has made me thinking. Couldn't this be done directly in
> SQL?
>
>
> Brgds Robert
>