From: | Vincent AE Scott <pgsql-novice(at)codex(dot)net> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: IF...THEN...ELSE on INSERT |
Date: | 2001-12-07 13:13:13 |
Message-ID: | 20011207131313.A1438@majestic.ukshells.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Andrew McMillan(andrew(at)catalyst(dot)net(dot)nz)@Sat, Dec 08, 2001 at 12:56:29AM +1300:
> 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,
Thanks for that, and yes it does help somewhat, but it appears somewhat
cumbersome to me. It appears to me ( but i could be well out of whack )
that the function is losely linked to the table. If i change the table
definition ( like adding an extra column ) do i have to modify the
function aswell?
Is there anyway i can just modify the behaviour of an insert statement
to transparently do this for me. That way at least the database clients
dont have to implicitly know about this function/feature. Does that
make sense?
TIA,
-vince
--
PGP key: http://codex.net/pgp/pgp.asc
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Walter | 2001-12-07 13:20:02 | pg_ctl stop |
Previous Message | Andrew McMillan | 2001-12-07 11:56:29 | Re: IF...THEN...ELSE on INSERT |