Skip site navigation (1) Skip section navigation (2)

Re: IF...THEN...ELSE on INSERT

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 (view raw or flat)
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

In response to

Responses

pgsql-novice by date

Next:From: Oliver WalterDate: 2001-12-07 13:20:02
Subject: pg_ctl stop
Previous:From: Andrew McMillanDate: 2001-12-07 11:56:29
Subject: Re: IF...THEN...ELSE on INSERT

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group