replace into

mysql, replace into die x-te.
ich möchte in die tabelle einfügen, wenn der eintrag noch nicht da ist, und optimalerweise updaten, wenn der eintrag schon da ist.

bisher hab ich das in einem perlscript über zwei queries gelöst, ich hätte das aber gerne in einem. meine versuche bisher:

if exists (select id from names where hostname=‘hostname’)
update names set lastupdate=102 where hostname=‘hostname’
else
insert into names (hostname,lastupdate) values(‘hostname’,102);
ERROR: syntax error at or near “if” at character 1

alternativ:
insert into names (hostname,lastupdate) values(‘hostname’,102) where not (SELECT id from names WHERE hostname=‘hostname’);
ERROR: syntax error at or near “where” at character 73

bin ich zu blöd, das einigermassen performant und ohne haareraufen zu lösen?

ich arbeite mit psql 8.1.6 auf einem debian-like system.

ts

http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE

Andreas

DANKE!
ich hab das mal so eingefügt:

BEGIN
LOOP
UPDATE names SET lastupdate = lu WHERE hostname = hn;
IF found THEN
RETURN;
END IF;

BEGIN
INSERT INTO names(hostname,lastupdate) VALUES (hn, lu);
RETURN;
EXCEPTION WHEN unique_violation THEN
– do nothing
END;
END LOOP;
END;


das scheint auch zu funktionieren.

nun würde ich gerne noch einen status (0/1) zurückkriegen, ob nun eingefügt oder upgedatet wurde. ich mag kaum fragen, aber ich bin was
plpgsql angeht ein nullchecker…

tom

Du hast an 2 Stellen ein RETURN. Die jetzige function hat keinen Rückgabewert. Ändere das auf int. Dort, wo RETURN ist, gibst Du dann 0 oder 1 zurück. Das so anzupassen überlasse ich Dir zur Übung :wink:


Andreas

auch das mit 0/1 als return hab ich (nach einigen versuchen) noch hingekriegt.
weil die funktion so schön ist (elegant und um einiges schneller als die alte lösung), würde ich die auch für weitere queries verwenden wollen.

da möchte ich aber nicht nur 0/1 zurück, sondern die neue eingefügte id (also entweder die id des gefundenen eintrags (das kann ich), oder aber (und das krieg ich nicht mehr hin) die id des neuen eintrags.

die id ist ein handelsübliches “nextval(‘names_id_seq’::regclass)”-Feld.

thomas

currval(‘names_id_seq’::regclass)

Andreas

ich bin zu blöd - der teufel steckt schon im select:

BEGIN
LOOP
SELECT ID FROM uni_ips WHERE ip = ipnum;
IF found THEN
RETURN 0;
END IF;

BEGIN
INSERT INTO uni_ips(ip) VALUES (ipnum);
RETURN 1;
EXCEPTION WHEN unique_violation THEN
– do nothing
END;


END LOOP;
END;
xxx=# select get_ip_id(‘127.0.0.1’);
ERROR: SELECT query has no destination for result data
HINT: If you want to discard the results, use PERFORM instead.
CONTEXT: PL/pgSQL function “get_ip_id” line 3 at SQL statement

:confused:

bzgl. des currval: kann ich sicher sein, dass ich da den richtigen wert kriege, sprich, ist die tabelle während der ganzen funktion automatisch gelockt, oder muss ich mich um das locking selber kümmern, um zu verhindern, dass ein parallelprozess zwischen dem insert und dem return eine ip einschiebt und ich damit eine falsche id zurückbekomme?
oder wird die ganze funktion von pgsql als eine gekapselte transaktion behandelt und ich muss mich eh nicht ums locking kümmern?

thomas

Es, also PG, weiß nicht, was es mit dem Resultat Deines Selects machen soll. Du brauchst es ja auch nicht weiter. In dem Falle ist ein PERFORM anstelle des SELECTS zu verwenden. PG ist sogar so nett, Dir das zu sagen.


bzgl. des currval: kann ich sicher sein, dass ich da den richtigen wert kriege
thomas

Ja. Die Frage, ob currval() sicher sei, wurde schon 100 Millionen Mal gestellt und auch immer wieder so beantwortet.

Näheres im Handbuch:
http://www.postgresql.org/docs/8.2/interactive/sql-createsequence.html

(welches einen Fehler enthält, den Bruce hoffentlich bald fixt, er weiß es schon. Wer sich beeilt, kann ihn aber noch finden…)


Andreas

[quote=“akretschmer”]Es, also PG, weiß nicht, was es mit dem Resultat Deines Selects machen soll. Du brauchst es ja auch nicht weiter. In dem Falle ist ein PERFORM anstelle des SELECTS zu verwenden. PG ist sogar so nett, Dir das zu sagen.

Aber ich will ja ein select, weil ich das ergebnis (also uni_ips.id) zurückgeben möchte.

ändere ich die funktion so:

SELECT ID as ipid FROM uni_ips WHERE ip = ipnum;
IF found THEN
RETURN uni_ips.ipid;
END IF;
kommt (mit und ohne as, mit und ohne tabellenname) der selbe fehler wie oben beschrieben.

tom

Okay. Dann brauchst Du eine passende Variable und ein SELECT INTO variable …


Andreas

OK - ich hab die Function so weit, dass sie Funktioniert. Für currval bin ich zu doof - ich hab mir jetzt etwas umständlich (aber funktionierend) beholfen:

DECLARE
hostid integer;
BEGIN
LOOP
select into hostid id from names WHERE hostname = hn;
IF found THEN
RETURN (hostid-hostid-hostid);
END IF;

BEGIN
INSERT INTO names(hostname,lastupdate) VALUES (hn, lu);
EXCEPTION WHEN unique_violation THEN
– do nothing
END;

– für currval bin ich zu doof, deshalb das eigentlich überflüsseige konstrukt
BEGIN
SELECT INTO hostid id from names WHERE hostname=hn;
RETURN hostid;
END;

END LOOP;
END;