PL/pgSQL und FOUND

[Thomas Zuberbuehler <sa(at)zubi(dot)li>]

Grüezi ;-)

Ich habe ein Problem mit PL/pgSQL und der Variablen FOUND. Folgendes 
klappt bei mir nicht:

...
PERFORM * FROM person WHERE name = '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;
	
  BEGIN

    PERFORM md.information
    FROM geonames.metadata md
    WHERE (md.fid_geoobject = geoobj) AND (md.fid_metatype = mt);

    IF NOT FOUND THEN
      EXECUTE 'INSERT INTO geonames.metadata VALUES ( ' || geoobj || ', 
' || mt || ', ''' || inf || ''', ''' || s || ''', ''' || d ||''');';
    ELSE
      EXECUTE 'UPDATE geonames.metadata SET information = ''' ||
               inf || ''', source = ''' || s || ''', dateofcapture = 
''' || d || ''' WHERE (fid_geoobject = ' ||
               geoobj || ') AND (fid_metatype = ' || mt || ');';
    END IF;

    RETURN;
	
  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 <<