Thread: Updating & inserting in one shot!

In my Postgresql 8.06 db I have a table in which the key fields are a
date and a code.

Now I need to load data from a text file (or a
temporary table) that either should update OR should be insert-ed into
that table.

Is there any ready-to-use, contributed function allowing
this 1-shot update-insert into a table?

Ciao
Vittorio

nearly ready to use
replace (int4, text, "timestamp") with your fieldtypes; its convention: first param is primary key

replace
            update bcachekredbetr set
                betreuer=$2, letztespeicherung=$3
            where id_p=$1;
with the appropriate update (where clause -> references primary key)



CREATE OR REPLACE FUNCTION uoibcachekredbetr(int4, text, "timestamp")
  RETURNS int4 AS
$BODY$
        DECLARE
            result int4;
        BEGIN
            update bcachekredbetr set
                betreuer=$2, letztespeicherung=$3
            where id_p=$1;
       
        IF FOUND THEN
            result:=-1;
        else
           insert into bcachekredbetr (
           id_p, betreuer, letztespeicherung
           )
            values ($1, $2, $3);
            result:=$1;
        END IF;
            RETURN result;
       
        END;
        $BODY$
  LANGUAGE 'plpgsql' VOLATILE;


hth,

Harald







--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
PostgreSQL - supported by a community that does not put you on hold