Grüezi ;-)
Ich habe ein Problem mit PL/pgSQL und der Variablen FOUND. Folgendes
klappt bei mir nicht:
....
PERFORM * FROM person WHERE name =3D 'Florian';
IF NOT FOUND THEN
...
ELSE
...
END IF;
....
------------------------------------------------------------ -------------
Ich habe mal die ganze Funktion gepostet:
BEGIN;
CREATE
FUNCTION geonames.update_or_create_metadata(BIGINT, INTEGER, TEXT,
TEXT, TEXT)
RETURNS void AS $BODY$
DECLARE
geoobj ALIAS FOR $1;
mt ALIAS FOR $2;
inf ALIAS FOR $3;
s ALIAS FOR $4;
d ALIAS FOR $5;
ret TEXT;
=09
BEGIN
PERFORM md.information
FROM geonames.metadata md
WHERE (md.fid_geoobject =3D geoobj) AND (md.fid_metatype =3D mt);
IF NOT FOUND THEN
EXECUTE 'INSERT INTO geonames.metadata VALUES ( ' || geoobj || ',
' || mt || ', ''' || inf || ''', ''' || s || ''', ''' || d ||''');';
ELSE
EXECUTE 'UPDATE geonames.metadata SET information =3D ''' ||
inf || ''', source =3D ''' || s || ''', dateofcapture =3D=
''' || d || ''' WHERE (fid_geoobject =3D ' ||
geoobj || ') AND (fid_metatype =3D ' || mt || ');';
END IF;
RETURN;
=09
END;
$BODY$ LANGUAGE plpgsql;
COMMIT;
------------------------------------------------------------ -------------
Die Tabelle:
CREATE TABLE geonames.metadata (
fid_metatype INTEGER,
fid_geoobject BIGINT,
information TEXT NOT NULL,
source TEXT,
dateofcapture TEXT,
PRIMARY KEY (fid_geoobject, fid_metatype),
FOREIGN KEY (fid_metatype) REFERENCES geonames.metatype
ON DELETE CASCADE,
FOREIGN KEY (fid_geoobject) REFERENCES geonames.geoobject
ON DELETE CASCADE
);
------------------------------------------------------------ -------------
Eigendlich sollte folgenden Aufruf keinen Error geben, da ja UPDATE und
nicht INSERT aufgerufen werden:
SELECT geonames.update_or_create_metadata(16, 22, 'xyz', 'b', '');
ERROR: duplicate key violates unique constraint "metadata_pkey"
KONTEXT: SQL statement "INSERT INTO geonames.metadata VALUES ( 16, 22,
'xyz', 'b', '');"
PL/pgSQL function "update_or_create_metadata" line 19 at execute statement
------------------------------------------------------------ -------------
Ich hoffe jemand kann mir helfen? Ich bin ziemlich ratlos und nach 2
Stunden recherche am Ende meines Lateins.
Vielen Dank bereits im Voraus.
Freundliche Grüsse
Thomas Zuberbühler
--
Thomas Zuberbühler
http://www.zubi.li
____________________________________________________________ __________
>> GPS Access for Java, http://www.gafj.net, http://gafj.tigris.org <<
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo [at] postgresql.org so that your
message can get through to the mailing list cleanly
