[PostgreSQL] IF-voorwaarde doet niet wat gevraagd is.

[Jackthe]

Over het onderstaande probleem breek me al enkele dagen het hoofd, wellicht hebben jullie een idee.

Database: Postgresql

Tables:

SQL:

CREATE TABLE gegevens (
  sleutel  SERIAL PRIMARY KEY,
  telefoonnummer          VARCHAR(20) NOT NULL UNIQUE,
  straatnaam              VARCHAR(24) NOT NULL,
  straatnaam_toevoeging   VARCHAR(2),
  woonplaats              VARCHAR(24) NOT NULL,
  voornaam                TEXT,
  tussenvoegsel           TEXT,
  naam                    TEXT NOT NULL,
  mutatie    TIMESTAMP
);
CREATE TABLE gegevens_update (
  sleutel  INTEGER NOT NULL PRIMARY KEY,
  telefoonnummer          VARCHAR(20),
  straatnaam              VARCHAR(24),
  straatnaam_toevoeging   VARCHAR(2),
  woonplaats              VARCHAR(24),
  voornaam                TEXT,
  tussenvoegsel           TEXT,
  naam                    TEXT,
  mutatie    TIMESTAMP
);

Gewenst gedrag:
Wanneer er een update op gegevens plaatsvindt en er geen entry is in gegevens_update met dezelfde sleutel moet de oude entry in gegevens_update geplaatst worden.

Wanneer er een update op gegevens plaatsvindt en er reeds een entry in gegevens_update met dezelfde sleutel is EN {new}=={entry_in_gegevens_update} (uitgezonderd mutatie), verwijder entry uit gegevens_update.

Function die hier voor moet zorgen, is trigger die na UPDATE plaatsvindt:

SQL:

CREATE OR REPLACE FUNCTION update_entry() RETURNS trigger AS '
DECLARE
      update gegevens_update%ROWTYPE;
      BEGIN
        SELECT INTO update * FROM gegevens_update WHERE sleutel = OLD.sleutel;
        IF NOT FOUND AND NOT (
          OLD.sleutel  =  NEW.sleutel  AND
          OLD.telefoonnummer          =  NEW.telefoonnummer          AND
          OLD.straatnaam              =  NEW.straatnaam              AND
          (OLD.straatnaam_toevoeging   =  NEW.straatnaam_toevoeging   OR
          (OLD.straatnaam_toevoeging IS NULL AND NEW.straatnaam_toevoeging IS NULL)) AND
          OLD.woonplaats              =  NEW.woonplaats              AND
          (OLD.voornaam                =  NEW.voornaam                OR
          (OLD.voornaam IS NULL AND NEW.voornaam IS NULL))  AND
          (OLD.tussenvoegsel           =  NEW.tussenvoegsel           OR
          (OLD.tussenvoegsel IS NULL AND NEW.tussenvoegsel IS NULL))  AND
          OLD.naam                    =  NEW.naam)
       THEN 
           INSERT INTO gegevens_update(...<snip>...);
      ELSIF FOUND AND (
          update.sleutel  =  NEW.sleutel  AND
          update.telefoonnummer          =  NEW.telefoonnummer          AND
          update.straatnaam              =  NEW.straatnaam              AND
          (update.straatnaam_toevoeging   =  NEW.straatnaam_toevoeging   OR
          (update.straatnaam_toevoeging IS NULL AND NEW.straatnaam_toevoeging IS NULL)) AND
          update.woonplaats              =  NEW.woonplaats              AND
          (update.voornaam                =  NEW.voornaam                OR
          (update.voornaam IS NULL AND NEW.voornaam IS NULL))  AND
          (update.tussenvoegsel           =  NEW.tussenvoegsel           OR
          (update.tussenvoegsel IS NULL AND NEW.tussenvoegsel IS NULL))  AND
          update.naam                    =  NEW.naam)
       THEN 
          DELETE FROM gegevens_update WHERE sleutel = NEW.sleutel;
        END IF;
        RETURN NEW;
    END;
' LANGUAGE plpgsql;

Dit alles gaat prima wijziging in de velden: telefoonnummer, straatnaam, woonplaats en naam.

Maar het gaat verkeerd als er een wijziging plaatsvindt in straatnaam_toevoeging, voornaam of tussenvoegsel. Let op! Wijziging gaat van NULL naar een waarde.

Ik heb dus al geprobeerd om het op te lossen met:

SQL:

(update.tussenvoegsel           =  NEW.tussenvoegsel           OR
(update.tussenvoegsel IS NULL AND NEW.tussenvoegsel IS NULL))

Maar dit werkt niet. Wellicht hebben jullie een idee?

[Soultaker]

Hmz, ik vind het moeilijk om te begrijpen wat de gewenste functionaliteit nu precies is.

Jackthe schreef op woensdag 01 februari 2006 @ 16:08:
Wanneer er een update op gegevens plaatsvindt en er reeds een entry in gegevens_update met dezelfde sleutel is EN {new}=={entry_in_gegevens_update} (uitgezonderd mutatie), verwijder entry uit gegevens_update.

Dit stukje snap ik niet helemaal. Is {new} 'uitgezonderd mutatie' niet gewoon '{old}'?

Uit je code begrijp ik dat je de 'gegevens_update' entry niet wilt instellen als 'gegevens' niet gewijzigd wordt behalve als 'ie al bestaat - dan wordt 'ie verwijderd ongeacht of er een wijziging in 'gegevens' is of niet. Dat vind ik nogal raar.

Dit alles gaat prima wijziging in de velden: telefoonnummer, straatnaam, woonplaats en naam.

Maar het gaat verkeerd als er een wijziging plaatsvindt in straatnaam_toevoeging, voornaam of tussenvoegsel. Let op! Wijziging gaat van NULL naar een waarde.

Wat gaat er precies verkeerd? Over welk van de twee scenario's (gegevens_update entry toevoegen of verwijderen?) hebben we het?

[Jackthe]

Soultaker schreef op woensdag 01 februari 2006 @ 16:48:
Hmz, ik vind het moeilijk om te begrijpen wat de gewenste functionaliteit nu precies is.

[...]

Dit stukje snap ik niet helemaal. Is {new} 'uitgezonderd mutatie' niet gewoon '{old}'?

Ik bedoel met 'uitgezonderd mutatie' het veld mutatie (timestamp). Sorry, is een beetje verwarrend.

Uit je code begrijp ik dat je de 'gegevens_update' entry niet wilt instellen als 'gegevens' niet gewijzigd wordt

klopt!

behalve als 'ie al bestaat - dan wordt 'ie verwijderd ongeacht of er een wijziging in 'gegevens' is of niet. Dat vind ik nogal raar.

Klopt niet, als hij gevonden (ELSIF FOUND) is dan wordt hij vergeleken met de entry die reeds in 'gegevens_update' staat. Wanneer hij dan gelijk is moet de entry uit 'gegevens_update' verwijderd worden.

Wat gaat er precies verkeerd? Over welk van de twee scenario's (gegevens_update entry toevoegen of verwijderen?) hebben we het?

Beide gaat het verkeerd. Het probleem ligt iets genuanceerder, ik zal een voorbeeld geven:
Gegevens
sleutel || telefoonnummer || straatnaam || straatnaam_toevoeging || woonplaats || voornaam || tussenvoegsel || naam || mutatie
12 || 012-3456789 || teststrat || || Teststad || voortest || || achtertest || 2006-01-31 12:06

code:

UPDATE gegevens SET straatnaam='teststraat' WHERE sleutel=12;

met deze query wordt {old} in 'gegevens_update' geplaatst. Als daarna

code:

UPDATE gegevens SET straatnaam='teststrat' WHERE sleutel=12;

dan wordt deze weer verwijderd uit 'gegevens_update'. Want {update}=={new}

code:

UPDATE gegevens SET tussenvoegsel='van' WHERE sleutel=12;

resulteert er in dat 'gegevens' wel wordt ge-updated maar {old} wordt niet in 'gegevens_update' geplaatst

[Soultaker]

Aha, ik denk dat ik 'm nu begrijp.

Volgens mij gaat het fout op die NULL values, omdat een expressie met NULL erin naar NULL evalueert (uit m'n hood, dus correct me if I'm wrong ).

Als OLD.tussenvoegsel NULL is en NEW.tussenvoegsel 'van', dan evalueert de expressie OLD.tussenvoegsel = NEW.tussenvoegsel OR (OLD.tussenvoegsel IS NULL AND NEW.tussenvoegsel IS NULL)) dus naar NULL OR FALSE wat gelijk is aan NULL.

Dan zit er in je reeks AND expressies dus óók een NULL waarde: TRUE AND TRUE ... AND NULL evalueert óók naar NULL. Vervolgens doe je dus NOT NULL wat óók NULL oplevert.

[Jackthe]

Dat is inderdaad het probleem. Nu nog het probleem oplossen

Iemand het geniale idee???

[Jackthe]

DAMN, dat ben ik zelf

Oplossing:

code:

((OLD.tussenvoegsel IS NULL AND NEW.tussenvoegsel IS NULL) OR 
(OLD.tussenvoegsel IS NOT NULL AND NEW.tussenvoegsel IS NOT NULL AND OLD.tussenvoegsel=NEW.tussenvoegsel))

[Soultaker]

Ik zat zelf te denken aan COALESCE(A = B, A IS NULL AND B IS NULL) maar dit werkt ook.

[ Voor 22% gewijzigd door Soultaker op 01-02-2006 19:04 ]