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
>