Duplicate key update

Unter Mysql funktioniert ja dieser Befehl, under PG nicht:

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; UPDATE table SET c=c+1 WHERE a=1;
Ich hab mir daher eine Funktion geschrieben die 5 Werte erhält ( var1 - var5 )
Die insert Funktion läuft wie sie soll, nur der Update geht schief !
Spaltenverweis »e« ist nicht eindeutig at character 65DETAIL: Er könnte sich entweder auf eine PL/pgSQL-Variable oder eine Tabellenspalte beziehen.

Die Funktion:

LOOP

UPDATE table SET “e” = (var5) WHERE “a” = (var1) AND “b” = (var2);
IF found THEN RETURN;
END IF;
BEGIN
INSERT INTO table (a, b, c, d, e)
VALUES ((var1), (var2), (var3), (var4), (var5));
RETURN;
EXCEPTION WHEN unique_violation THEN
END;
END LOOP;

Ich hab seither nichts gefunden, was mir da irgendwie weiterhilft. Es muss doch möglich sein auch im Update die variablen zu benutzen.
Vermute mal mir fehlen irgendwo wieder () oder ’ , hab alle Kombinationen irgendwie auch schon probiert aber seh wohl geade den einen Baum nicht !!

Wenn Du 9.1 verwendest, geht das auch mit einem einzigen SQL Statement:

WITH new_values (a,b,c,d,e) as (
  values 
     (1, 2, 3, 4, 5)

),
upsert as
( 
    UPDATE mytable m 
        set m.e = nv.e
    FROM new_values nv
    WHERE m.a = nv.a
      AND m.b = nv.b
    RETURNING m.*
)
INSERT INTO mytable (a,b,c,d,e)
SELECT a,b,c,d,e
FROM new_values
WHERE NOT EXISTS (SELECT 1 
                  FROM upsert up 
                  WHERE up.a = new_values.a
                    AND up.b = new_values.b)

Wenn Du mehr als einen Datensatz ändern/einfügen willst, dann häng die weiteren Werte einfach in den “VALUES” Teil:

WITH new_values (a,b,c,d,e) as (
  values 
     (1, 2, 3, 4, 5),
     (10,20,30,40,50),
     (100,200,300,400,500)
),
....

Nein da wird immer ein Datensatz geändert bzw, eingefügt.
Die Funktion bekommt 5 Werte übermittelt, wenn der Datensatz existiert WHERE “a” = (var1) AND “b” = (var2) soll nur das Feld e aktualisiert werden ( Zeitstempel aus der Applikation ) ( var1 und var2 sind ein zusammengesetzter Primärschlüssel ).

Werd mal die Funktion umschreiben und testen.

Danke !

Das ist exakt was mein Statement macht.

Mit festen Werten für einen Datensatz kann man das auch etwas einfacher schreiben:

WITH upsert as
( 
    UPDATE mytable m 
        set m.e = 5
    FROM new_values nv
    WHERE m.a = 1
      AND m.b = 2
    RETURNING m.*
)
INSERT INTO mytable (a,b,c,d,e)
SELECT 1,2,3,4,5
FROM new_values
WHERE NOT EXISTS (SELECT 1 FROM upsert up)



Du müsstest schon die gesamte Funktion hier posten, ich werde aus dem Beispiel nicht schlau

Und bitte pack um Dein SQL

 Tags, damit das lesbar wird. Näheres dazu hier: http://www.pg-forum.de/misc.php?do=bbcode

Vielleicht ist das verständlicher !

CREATE OR REPLACE FUNCTION safetytr.func_masterdata
(
IN line integer,
IN shift integer,
IN points integer,
IN firstinput timestamp WITHOUT TIME ZONE,
IN actualtime timestamp WITHOUT TIME ZONE
)
RETURNS SETOF integer AS
$$
begin
WITH new_values (var1, var2, var3, var4, var5)
as (
VALUES (line,shift,points,firstinput,actualtime)
),
upsert as
(
UPDATE safetytr.triggerinputmaster m
SET m.var5 = nv.var5
FROM new_values nv
WHERE m.var1 =nv.var1
AND m.var2 = nv.var2
RETURNING m.*
)
INSERT INTO safetytr.triggerinputmaster (var1, var2, var3, var4, var5)
SELECT var1, var2, var3, var4, var5
FROM new_values
WHERE NOT EXISTS (SELECT 1
FROM upsert up
WHERE up.var1 =new_values.var1
AND up.var2 = new_values.var2)

end
$$

Hab das jetzt mal umgeschrieben !
Entweder bin ich gerade einfach zu blöd es zu kapieren oder ich steh auf meiner Leitung.

CREATE OR REPLACE FUNCTION safetytr.func_masterdata
(
[ident] IN line integer,
IN shift integer,
IN points integer,
IN firstinput timestamp WITHOUT TIME ZONE,
IN actualtime timestamp WITHOUT TIME ZONE
[/ident]
)
RETURNS SETOF integer AS
$$
begin
[ident] WITH new_values (var1, var2, var3, var4, var5)
as (
VALUES (line,shift,points,firstinput,actualtime)
[/ident]
),
upsert as
(
UPDATE safetytr.triggerinputmaster m
[ident] SET m.var5 = nv.var5
FROM new_values nv
WHERE m.var1 =nv.var1
AND m.var2 = nv.var2
RETURNING m.*
[/ident]
)
INSERT INTO safetytr.triggerinputmaster (var1, var2, var3, var4, var5)
SELECT var1, var2, var3, var4, var5
FROM new_values
WHERE NOT EXISTS (SELECT 1
[ident] FROM upsert up
WHERE up.var1 =new_values.var1
AND up.var2 = new_values.var2)
[/ident]
end
$$

CREATE OR REPLACE FUNCTION safetytr.func_masterdata
([INDENT]IN line integer,[/INDENT]
[INDENT]IN shift integer,[/INDENT]
[INDENT]IN points integer,[/INDENT]
[INDENT]IN firstinput timestamp WITHOUT TIME ZONE,
[/INDENT]
[INDENT]IN actualtime timestamp WITHOUT TIME ZONE
[/INDENT]
)
RETURNS SETOF integer AS
$$
begin[INDENT][ident] WITH new_values (var1, var2, var3, var4, var5)[/INDENT]
[INDENT=2]as ([/INDENT]
[INDENT=2]VALUES (line,shift,points,firstinput,actualtime)
[/INDENT]
),
upsert as
(
UPDATE safetytr.triggerinputmaster m [INDENT]SET m.var5 = nv.var5[/INDENT]
[INDENT]FROM new_values nv[/INDENT]
[INDENT]WHERE m.var1 =nv.var1 [/INDENT]
[INDENT=2]AND m.var2 = nv.var2
[/INDENT]
[INDENT]RETURNING m.*
[/INDENT]
)
INSERT INTO safetytr.triggerinputmaster (var1, var2, var3, var4, var5)
SELECT var1, var2, var3, var4, var5
FROM new_values
WHERE NOT EXISTS (SELECT 1[INDENT]FROM upsert up[/INDENT]
[INDENT=2]WHERE up.var1 =new_values.var1 [/INDENT]
[INDENT=2]AND up.var2 = new_values.var2)
[/INDENT]

end
$$


So sieht das gerade aus, hoffe das ist verständlicher.

Mit welcher Sprache ist das? SQL oder PLpgSQL?

Denn mit einer SQL Funktion kannst Du nur über Positionen ($1, $2 usw) auf die Parameter zugreifen. Mit PLpgSQL könntest Du auch via Namen zugreifen

Mein vereinfachtes Beispiel war übrigens falsch, aber ich kann den Beitrag nicht mehr editieren:

WITH upsert as
( 
    UPDATE mytable m 
        set m.e = 5
    WHERE m.a = 1
      AND m.b = 2
    RETURNING m.*
)
INSERT INTO mytable (a,b,c,d,e)
SELECT 1,2,3,4,5
FROM new_values
WHERE NOT EXISTS (SELECT 1 FROM upsert up)

(Da war noch ein FROM new_values drin, was da nicht hingehört)

Hab ich wohl die wichtigste Zeile meiner Funktion vergessen.
LANGUAGE ‘plpgsql’

Kann mir vielleicht trotzdem jemand noch sagen warum in meiner ersten Frage ein INSERT funktioniert aber kein UPDATE ?
Man will ja nicht dumm sterben !!

ok, hab das jetzt mal anders probiert:
statt
UPDATE table SET “e” = (var5) WHERE “a” = (var1) AND “b” = (var2);
hab ich
UPDATE table SET “e” = now() WHERE “a” = (var1) AND “b” = (var2);
und dann läüft es, hatte da wohl einen kleinen Denkfehler drin.

Kleine Anmerkung: sowohl die Klammern um die Variablen als auch die Anführungszeichen sind überflüssig.

Warum das Orginal nicht funktioniert kann ich nicht sagen (da fehlt die halbe Funktione)

Mein Statement war eigentlich als komplette Ersatz für die Funktion gedacht. Deine ursprüngliche Idee war ja ein “update or insert” in einen Aufruf (= 1 Statement) zu verpacken. Mit dem “writeable CTE” kannst Du das direkt als ein Statement laufen lassen, ohne eine Funktion erstellen zu müssen.


Die Fehlermeldung klingt aber danach als hättest Du eine Variable mit dem Namen e und eine Spalte mit dem Namen e.

Und zum Schluss nochmal die Bitte SQL Statements in

 Tags einzuschliessen (so wie ich es gemacht habe)
Die Erläuterungen dazu sind hier: http://www.pg-forum.de/misc.php?do=bbcode
(Ausserdem auf jeder Seite unten Rechts unter dem Titel "BB-Code ist an")

gefällt mir :wink:

Andreas

Ist inspiriert von diesem Blog hier:
Upserting via Writeable CTE - zillablog

Ich habe es nur erweitert um auch mehr als einen Datensatz angeben zu können.
Ich denke, näher kommt man an ein echtes MERGE Statement momentan nicht ran (aber das kommt ja so wie es aussieht in 9.2 :wink: )