Обсуждение: IF...THEN...ELSE on INSERT

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.

your TIA,
-vince

--

PGP key:  http://codex.net/pgp/pgp.asc

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

Andrew McMillan(andrew@catalyst.net.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

On Sat, 2001-12-08 at 02:13, Vincent AE Scott wrote:
>
> 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?

Yep, that does make sense.  What you are asking for is effectively an
'INSERT OR UPDATE <table> (<field list> ) VALUES( <value list> ) ...'
syntax.

This is being looked at in some areas ("CREATE OR REPLACE FUNCTION",
"CREATE OR REPLACE VIEW", ...) for 7.3, I believe.  Whether or not there
will be an "INSERT OR UPDATE ..." statement included in there I am not
sure.  I am pretty sure it has been proposed, and I know it would make
things easier for many programmers, myself included :-)

I think that it might be possible to write a function to do what you
want generically in 7.1 or later, but it would be complex.  PL/PGSQL
will let you PERFORM a query you have constructed as a string, and you
can pass a row to a function, so you could (perhaps) do something very
complicated and unmaintainable there.

You might be better with a more complex language like PL/Perl for
something along those lines, or with a C function.

Regards,
                    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