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
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(at)tripnet(dot)se> wrote in message
news:1129790184(dot)351579(dot)302550(at)g47g2000cwa(dot)googlegroups(dot)com(dot)(dot)(dot)
> 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
>