Re: automatic update or insert

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
ANDdmc_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;
RETURNNULL;
 
 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
>