From: | Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz> |
---|---|
To: | Vincent AE Scott <pgsql-novice(at)codex(dot)net> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: IF...THEN...ELSE on INSERT |
Date: | 2001-12-07 11:56:29 |
Message-ID: | 1007726189.6712.3.camel@kant.mcmillan.net.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Sat, 2001-12-08 at 00:34, Vincent AE Scott wrote:
> What's the fastest / most effecient way todo a test on an insert
> operation and then change the SQL that gets exeuted?
>
> Specifically, i want to check if a row already exists, and then instead
> of inserting the row, just increment a counter for that row.
I usually find this best implemented in a PL/PGSQL function:
CREATE FUNCTION myfunc( TEXT, NUMBER ) RETURNS INT4 AS '
DECLARE
p_1 ALIAS FOR $1;
p_2 ALIAS FOR $2;
curr_val TEXT;
BEGIN
SELECT <something> INTO curr_val FROM mytable
WHERE table_id = p_1;
IF FOUND THEN
UPDATE mytable SET upfield = p_2
WHERE table_id = p_1;
ELSE
INSERT INTO mytable (table_id, upfield)
VALUES( p_1, p_2 );
END IF;
RETURN <whatever>;
END;
' LANGUAGE 'plpgsql';
Hope that helps,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
From | Date | Subject | |
---|---|---|---|
Next Message | Vincent AE Scott | 2001-12-07 13:13:13 | Re: IF...THEN...ELSE on INSERT |
Previous Message | Vincent AE Scott | 2001-12-07 11:34:06 | IF...THEN...ELSE on INSERT |