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

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