From: codeWarrior <gpatnude@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: automatic update or insert
Date: Tue, 25 Oct 2005 08:26:16 -0700
Message-ID: <djlinr$2mu1$1@news.hub.org> (raw)
References: <1129790184.351579.302550@g47g2000cwa.googlegroups.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gpatnude@hotmail.com
Subject: Re: automatic update or insert
In-Reply-To: <djlinr$2mu1$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox