ืคึผืจืึธื”ืึธืกื˜ืขืจ > ื‘ืœืึธื’ > ืึทื“ืžื™ื ื™ืกื˜ืจืึทืฆื™ืข > PostgreSQL Antipatterns: ืฆื•ืฉื˜ืึทื ื“ ืขื•ื•ืึทืœื•ืึทื˜ื™ืึธืŸ ืื™ืŸ SQL

PostgreSQL Antipatterns: ืฆื•ืฉื˜ืึทื ื“ ืขื•ื•ืึทืœื•ืึทื˜ื™ืึธืŸ ืื™ืŸ SQL

SQL ืื™ื– ื ื™ืฉื˜ C ++ ืื•ืŸ ื ื™ืฉื˜ ื“ื–ืฉืึทื•ื•ืึทืกืงืจื™ืคึผื˜. ื“ืขืจื™ื‘ืขืจ, ื“ื™ ื›ืขื–ืฉื‘ืŸ ืคื•ืŸ ืœืึทื“ื–ืฉื™ืงืึทืœ ืื•ื™ืกื“ืจื•ืงืŸ ืึทืงืขืจื– ืึทื ื“ืขืจืฉ, ืื•ืŸ ื“ืึธืก ืื™ื– ื’ืึธืจ ื ื™ืฉื˜ ื“ื™ ื–ืขืœื‘ืข ื–ืึทืš:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

ืื™ืŸ ื“ืขื ืคึผืจืึธืฆืขืก ืคื•ืŸ ืึธืคึผื˜ื™ืžื™ื–ื™ื ื’ ื“ื™ PostgreSQL ืึธื ืคึฟืจืขื’ ื“ื•ืจื›ืคื™ืจื•ื ื’ ืคึผืœืึทืŸ ืงืขื ืขืŸ ืึทืจื‘ื™ื˜ืจืขืจืึทืœื™ "ืจื™ืขืจื™ื™ื ื“ื–ืฉ" ืขืงื•ื•ื™ื•ื•ืึทืœืขื ื˜ ื˜ื ืึธื™ื, ื ื™ืฉื˜ ืื•ื™ืกืจืขื›ืขื ืขืŸ ื˜ื™ื™ืœ ืคื•ืŸ ื–ื™ื™ ืคืืจ ืื™ื ื“ื™ื•ื•ื™ื“ื•ืืœืข ืจืขืงืืจื“ืก, ืคืืจื‘ื™ื ื“ืŸ ื–ื™ื™ ืฆื• ื“ื™ ื‘ืื“ื™ื ื’ื•ื ื’ืขืŸ ืคื•ื ืขื ืื™ื™ื ื’ืขื•ื•ืขื ื“ื˜ืŸ ืื™ื ื“ืขืงืก... ื‘ืงื™ืฆื•ืจ, ื“ืขืจ ื’ืจื™ื ื’ืกื˜ืขืจ ื•ื•ืขื’ ืื™ื– ืฆื• ืื ื ืขืžืขืŸ ืื– ืื™ืจ ืงืขื ืขืŸ ื ื™ืฉื˜ ืงืึธื ื˜ืจืึธืœื™ืจืŸ ืื™ืŸ ื•ื•ืึธืก ืกื“ืจ ื–ื™ื™ ื•ื•ืขืœืŸ (ืื•ืŸ ืฆื™ ื–ื™ื™ ื•ื•ืขืœืŸ ื–ื™ื™ืŸ ืงืึทืœืงื™ืึทืœื™ื™ื˜ื™ื“ ืื™ืŸ ืึทืœืข) ื’ืœื™ื™ึทืš ื‘ืื“ื™ื ื’ื•ื ื’ืขืŸ.

ื“ืขืจื™ื‘ืขืจ, ืื•ื™ื‘ ืื™ืจ ื ืึธืš ื•ื•ื™ืœืŸ ืฆื• ืคื™ืจืŸ ื‘ื™ืœื›ืขืจืงื™ื™ึทื˜, ืื™ืจ ื“ืึทืจืคึฟืŸ ืฆื• ืกื˜ืจื•ืงื˜ื•ืจ ืขืก ืžืึทื›ืŸ ื“ื™ ื‘ืื“ื™ื ื’ื•ื ื’ืขืŸ ืึทื ื™ืงื•ื•ืึทืœ ื ื™ืฆืŸ ืงืึทื ื“ื™ืฉืึทื ืึทืœื– ืื•ื™ืกื“ืจื•ืงืŸ ะธ ืึธืคึผืขืจื™ื™ื˜ืขืจื–.

PostgreSQL Antipatterns: ืฆื•ืฉื˜ืึทื ื“ ืขื•ื•ืึทืœื•ืึทื˜ื™ืึธืŸ ืื™ืŸ SQL
ื“ืึทื˜ืŸ ืื•ืŸ ืืจื‘ืขื˜ืŸ ืžื™ื˜ ื–ื™ื™ ื–ืขื ืขืŸ ื“ื™ ื™ืงืขืจ ืื•ื ื“ื–ืขืจ VLSI ืงืึธืžืคึผืœืขืงืก, ื“ืขืจื™ื‘ืขืจ ืขืก ืื™ื– ื–ื™ื™ืขืจ ื•ื•ื™ื›ื˜ื™ืง ืคึฟืึทืจ ืื•ื ื“ื– ืึทื– ืึทืคึผืขืจื™ื™ืฉืึทื ื– ืื•ื™ืฃ ื–ื™ื™ ื–ืขื ืขืŸ ื ื™ืฉื˜ ื‘ืœื•ื™ื– ืจื™ื›ื˜ื™ืง, ืึธื‘ืขืจ ืื•ื™ืš ื™ืคื™ืฉืึทื ื˜ืœื™. ื–ืืœ ืก ืงื•ืง ืื™ืŸ ืกืคึผืขืฆื™ืคื™ืฉ ื‘ื™ื™ืฉืคื™ืœืŸ ื•ื•ื• ืขืจืจืึธืจืก ืื™ืŸ ืงืึทืœืงื™ืึทืœื™ื™ื˜ื™ื ื’ ืื•ื™ืกื“ืจื•ืงืŸ ืงืขื ืขืŸ ื–ื™ื™ืŸ ื’ืขืžืื›ื˜, ืื•ืŸ ื•ื•ื• ืขืก ืื™ื– ื•ื•ืขืจื˜ ื™ืžืคึผืจื•ื•ื•ื™ื ื’ ื–ื™ื™ืขืจ ืขืคืขืงื˜ื™ื•ื•ืงื™ื™ึทื˜.

#0: RTFM

ืึธื ื”ื™ื™ื‘ ื‘ื™ื™ึทืฉืคึผื™ืœ ืคื•ืŸ ื“ืึทืงื™ื•ืžืขื ื˜ื™ื™ืฉืึทืŸ:

ื•ื•ืขืŸ ื“ืขืจ ืกื“ืจ ืคื•ืŸ ืืคืฉืืฆื•ื ื’ ืื™ื– ื•ื•ื™ื›ื˜ื™ืง, ืขืก ืงืขื ืขืŸ ื–ื™ื™ืŸ ืงืึทืคึผื˜ืฉืขืจื“ ืžื™ื˜ ื“ื™ ืงืึทื ืกื˜ืจืึทืงืฉืึทืŸ CASE. ืคึฟืึทืจ ื‘ื™ื™ึทืฉืคึผื™ืœ, ื“ืึธืก ืื™ื– ืึท ื•ื•ืขื’ ืฆื• ื•ื™ืกืžื™ื™ื“ืŸ ื“ื™ื˜ื™ื™ืœื“ ื“ื•ืจืš ื ื•ืœ ืื™ืŸ ืึท ื–ืึทืฅ WHERE ื ื™ื˜ ืคืึทืจืœืึธื–ืœืขืš:

SELECT ... WHERE x > 0 AND y/x > 1.5;

ื–ื™ื›ืขืจ ืึธืคึผืฆื™ืข:

SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

ื“ืขืจ ืคึผืœืึทืŸ ื’ืขื ื™ืฆื˜ ืื™ืŸ ื“ืขื ื•ื•ืขื’ CASE ืคึผืจืึทื˜ืขืงืฅ ื“ื™ ืื•ื™ืกื“ืจื•ืง ืคื•ืŸ ืึทืคึผื˜ืึทืžืึทื–ื™ื™ืฉืึทืŸ, ืึทื–ื•ื™ ืขืก ื–ืึธืœ ื–ื™ื™ืŸ ื’ืขื•ื•ื™ื™ื ื˜ ื‘ืœื•ื™ื– ื•ื•ืขืŸ ื ื™ื™ื˜ื™ืง.

#1: ืฆื™ื ื’ืœ ืฆื•ืฉื˜ืึทื ื“

BEGIN
  IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;

ืึทืœืฅ ืžื™ื™ื ื˜ ืฆื• ืงื•ืงืŸ ื’ื•ื˜, ืึธื‘ืขืจ ... ืงื™ื™ืŸ ืื™ื™ื ืขืจ ื”ื‘ื˜ื—ื•ืช ืึทื– ื“ื™ ื™ื ื•ื•ืขืกืžืึทื ื˜ SELECT ื•ื•ืขื˜ ื ื™ื˜ ื–ื™ื™ืŸ ืขืงืกืึทืงื™ื•ื˜ืึทื“ ืื•ื™ื‘ ื“ืขืจ ืขืจืฉื˜ืขืจ ืฆื•ืฉื˜ืึทื ื“ ืื™ื– ืคืึทืœืฉ. ื–ืืœ ืก ืคืึทืจืจื™ื›ื˜ืŸ ืขืก ืžื™ื˜ ื ืขืกื˜ืขื“ IF:

BEGIN
  IF cond(NEW.fld) THEN
    IF EXISTS(SELECT ...) THEN
      ...
    END IF;
  END IF;
  RETURN NEW;
END;

ืื™ืฆื˜ ืœืึธื–ืŸ ืก ืงื•ืง ืงืขืจืคืึทืœื™ - ื“ื™ ื’ืื ืฆืข ื’ื•ืฃ ืคื•ืŸ ื“ื™ ืฆื™ื ื’ืœ ืคื•ื ืงืฆื™ืข ืื™ื– "ืืœื ื’ืขื•ื•ื™ืงืœื˜" ืื™ืŸ IF. ื“ืขื ืžื™ื˜ืœ ืึทื– ื’ืึธืจื ื™ืฉื˜ ืคึผืจื™ื•ื•ืขื ืฅ ืื•ื ื“ื– ืคื•ืŸ ืจื™ืžื•ื•ื•ื™ื ื’ ื“ืขื ืฆื•ืฉื˜ืึทื ื“ ืคื•ืŸ ื“ื™ ืคึผืจืึธืฆืขื“ื•ืจ ื ื™ืฆืŸ WHEN-ื‘ืื“ื™ื ื’ื•ื ื’ืขืŸ:

BEGIN
  IF EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;
...
CREATE TRIGGER ...
  WHEN cond(NEW.fld);

ื“ืขืจ ืฆื•ื’ืึทื ื’ ืื™ื– ื’ืขืจืึทื ื˜ื™ื“ ืฆื• ืจืึทื˜ืขื•ื•ืขืŸ ืกืขืจื•ื•ืขืจ ืจืขืกื•ืจืกืŸ ื•ื•ืขืŸ ื“ื™ ืฆื•ืฉื˜ืึทื ื“ ืื™ื– ืคืึทืœืฉ.

# 2: ืึธื“ืขืจ / ืื•ืŸ ืงื™ื™ื˜

SELECT ... WHERE EXISTS(... A) OR EXISTS(... B)

ืึทื ื“ืขืจืฉ, ืื™ืจ ืงืขื ืขืŸ ืกื•ืฃ ืึทืจื•ื™ืฃ ืžื™ื˜ ื‘ื™ื™ื“ืข EXISTS ื•ื•ืขื˜ ื–ื™ื™ืŸ "ืืžืช", ืึธื‘ืขืจ ื‘ื™ื™ื“ืข ื•ื•ืขืœืŸ ืžืงื•ื™ื ื•ื•ืขืจืŸ.

ืื‘ืขืจ ืื•ื™ื‘ ืžื™ืจ ื•ื•ื™ืกืŸ ืคึฟืึทืจ ื–ื™ื›ืขืจ ืึทื– ืื™ื™ื ืขืจ ืคื•ืŸ ื–ื™ื™ ืื™ื– "ืืžืช" ืคื™ืœ ืžืขืจ ืึธืคื˜ (ืึธื“ืขืจ "ืคืึทืœืฉ" - ืคึฟืึทืจ AND- ืงื™ื™ื˜ืŸ) - ืื™ื– ืขืก ืžืขื’ืœืขืš ืฆื• ืขืคืขืก "ืคืึทืจื’ืจืขืกืขืจืŸ ื–ื™ื™ึทืŸ ื‘ื™ืœื›ืขืจืงื™ื™ึทื˜" ืึทื–ื•ื™ ืึทื– ื“ื™ ืจื’ืข ืื™ื– ื ื™ืฉื˜ ืขืงืกืึทืงื™ื•ื˜ืึทื“ ืึทืžืึธืœ ื•ื•ื™ื“ืขืจ?

ืขืก ื˜ื•ืจื ืก ืื•ื™ืก ืึทื– ืขืก ืื™ื– ืžืขื’ืœืขืš - ื“ื™ ืึทืœื’ืขืจื™ื“ืึทื ืฆื•ื’ืึทื ื’ ืื™ื– ื ืึธืขื ื˜ ืฆื• ื“ื™ ื˜ืขืžืข ืคื•ืŸ โ€‹โ€‹ื“ืขื ืึทืจื˜ื™ืงืœ PostgreSQL Antipatterns: ืึท ื–ืขืœื˜ืŸ ืจืขืงืึธืจื“ ื•ื•ืขื˜ ื“ืขืจื’ืจื™ื™ื›ืŸ ื“ื™ ืžื™ื˜ืŸ ืคื•ืŸ ืึท JOIN.

ืœืึธืžื™ืจ ื ืึธืจ "ืฉื˜ื•ืคึผืŸ" ื‘ื™ื™ื“ืข ื“ื™ ื‘ืื“ื™ื ื’ื•ื ื’ืขืŸ ืื•ื ื˜ืขืจ CASE:

SELECT ...
WHERE
  CASE
    WHEN EXISTS(... A) THEN TRUE
    WHEN EXISTS(... B) THEN TRUE
  END

ืื™ืŸ ื“ืขื ืคืึทืœ ืžื™ืจ ื”ืึธื‘ืŸ ื ื™ืฉื˜ ื“ืขืคื™ื ื™ืจืŸ ELSE-ื•ื•ืขืจื˜, ื“ืึธืก ืื™ื–, ืื•ื™ื‘ ื‘ื™ื™ื“ืข ื‘ืื“ื™ื ื’ื•ื ื’ืขืŸ ื–ืขื ืขืŸ ืคืึทืœืฉ CASE ื•ื•ืขื˜ ืฆื•ืจื™ืงืงื•ืžืขืŸ NULL, ื•ื•ืึธืก ืื™ื– ื™ื ื˜ืขืจืคึผืจืึทื˜ืึทื“ ื•ื•ื™ FALSE ะฒ WHERE-ื‘ืื“ื™ื ื’ื•ื ื’ืขืŸ.

ื“ืขื ื‘ื™ื™ึทืฉืคึผื™ืœ ืงืขื ืขืŸ ื–ื™ื™ืŸ ืงืึทืžื‘ื™ื™ื ื“ ืื™ืŸ ืื ื“ืขืจืข ื•ื•ืขื’ืŸ - ื“ื™ืคึผืขื ื“ื™ื ื’ ืื•ื™ืฃ ื’ืขืฉืžืึทืง ืื•ืŸ ืงืึธืœื™ืจืŸ:

SELECT ...
WHERE
  CASE
    WHEN NOT EXISTS(... A) THEN EXISTS(... B)
    ELSE TRUE
  END

#3: ื•ื•ื™ [ื ื™ื˜] ืฆื• ืฉืจื™ื™ึทื‘ืŸ ื˜ื ืึธื™ื

ืžื™ืจ ืคืืจื‘ืจืื›ื˜ ืฆื•ื•ื™ื™ ื˜ืขื’ ืึทื ืึทืœื™ื™ื–ื™ื ื’ ื“ื™ ืกื™ื‘ื•ืช ืคึฟืึทืจ ื“ื™ "ืžืึธื“ื ืข" ืึธืคึผืขืจืึทืฆื™ืข ืคื•ืŸ โ€‹โ€‹ื“ืขื ืฆื™ื ื’ืœ - ืœืึธืžื™ืจ ื–ืขืŸ ื•ื•ืึธืก.

ืžืงื•ืจ:

IF( NEW."ะ”ะพะบัƒะผะตะฝั‚_" is null or NEW."ะ”ะพะบัƒะผะตะฝั‚_" = (select '"ะšะพะผะฟะปะตะบั‚"'::regclass::oid) or NEW."ะ”ะพะบัƒะผะตะฝั‚_" = (select to_regclass('"ะ”ะพะบัƒะผะตะฝั‚ะŸะพะ—ะฐั€ะฟะปะฐั‚ะต"')::oid)
     AND (   OLD."ะ”ะพะบัƒะผะตะฝั‚ะะฐัˆะฐะžั€ะณะฐะฝะธะทะฐั†ะธั" <> NEW."ะ”ะพะบัƒะผะตะฝั‚ะะฐัˆะฐะžั€ะณะฐะฝะธะทะฐั†ะธั"
          OR OLD."ะฃะดะฐะปะตะฝ" <> NEW."ะฃะดะฐะปะตะฝ"
          OR OLD."ะ”ะฐั‚ะฐ" <> NEW."ะ”ะฐั‚ะฐ"
          OR OLD."ะ’ั€ะตะผั" <> NEW."ะ’ั€ะตะผั"
          OR OLD."ะ›ะธั†ะพะกะพะทะดะฐะป" <> NEW."ะ›ะธั†ะพะกะพะทะดะฐะป" ) ) THEN ...

ืคึผืจืึธื‘ืœืขื #1: ื™ื ืึทืงื•ื•ืึทืœืึทื˜ื™ ื˜ื•ื˜ ื ื™ืฉื˜ ืจืขืกืคึผืขืงื˜ NULL

ื–ืืœ ืก ื™ืžืึทื“ื–ืฉืึทืŸ ืึทื– ืึทืœืฅ OLDโ€” ืคืขืœื“ืขืจ ื”ืื‘ืŸ ื’ืขื”ืื˜ ื‘ืื“ื™ื™ื˜ NULL. ื•ื•ืืก ื•ื•ืขื˜ ื’ืขืฉืขืŸ?

SELECT NULL <> 1 OR NULL <> 2;
-- NULL

ืื•ืŸ ืคื•ืŸ ื“ื™ ืคื•ื ื˜ ืคื•ืŸ ืžื™ื™ื ื•ื ื’ ืคื•ืŸ ืืจื‘ืขื˜ืŸ ืื•ื™ืก ื“ื™ ื‘ืื“ื™ื ื’ื•ื ื’ืขืŸ NULL ืขืงื•ื•ื™ื•ื•ืึทืœืขื ื˜ FALSE, ื•ื•ื™ ื“ืขืจืžืื ื˜ ืื•ื™ื‘ืŸ.

ื‘ืึทืฉื™ื™ื“: ื ื•ืฆืŸ ืึธืคึผืขืจืึทื˜ืึธืจ IS DISTINCT FROM ืคื•ืŸ ROW-ืึธืคึผืขืจืึทื˜ืึธืจ, ืงืึทืžืคึผืขืจื™ื ื’ ื’ืึทื ืฅ ืจืขืงืึธืจื“ืก ืื™ืŸ ืึทืžืึธืœ:

SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TRUE

ืคึผืจืึธื‘ืœืขื #2: ืคืึทืจืฉื™ื“ืขื ืข ื™ืžืคึผืœืึทืžืึทื ืฅ ืคื•ืŸ ื“ืขืจ ื–ืขืœื‘ื™ืงืขืจ ืคืึทื ื’ืงืฉืึทื ืึทืœื™ื˜ื™

ืœืืžื™ืจ ืคืืจื’ืœื™ื™ื›ืŸ:

NEW."ะ”ะพะบัƒะผะตะฝั‚_" = (select '"ะšะพะผะฟะปะตะบั‚"'::regclass::oid)
NEW."ะ”ะพะบัƒะผะตะฝั‚_" = (select to_regclass('"ะ”ะพะบัƒะผะตะฝั‚ะŸะพะ—ะฐั€ะฟะปะฐั‚ะต"')::oid)

ืคืืจื•ื•ืืก ืื™ื– ืขืก ืขืงืกื˜ืจืข ื™ื ื•ื•ืขืกืžืึทื ื˜ ื“ืึธ? SELECT? ื ืคึฟื•ื ืงืฆื™ืข to_regclass? ืคืืจื•ื•ืืก ืื™ื– ืขืก ืึทื ื“ืขืจืฉ? ..

ื–ืืœ ืก ืคืึทืจืจื™ื›ื˜ืŸ ืขืก:

NEW."ะ”ะพะบัƒะผะตะฝั‚_" = '"ะšะพะผะฟะปะตะบั‚"'::regclass::oid
NEW."ะ”ะพะบัƒะผะตะฝั‚_" = '"ะ”ะพะบัƒะผะตะฝั‚ะŸะพะ—ะฐั€ะฟะปะฐั‚ะต"'::regclass::oid

ืคึผืจืึธื‘ืœืขื #3: ื‘ื™ืœื›ืขืจืงื™ื™ึทื˜ ืคื•ืŸ ื‘ื•ืœ ืึทืคึผืขืจื™ื™ืฉืึทื ื–

ื–ืืœ ืก ืคึฟืึธืจืžืึทื˜ ื“ื™ ืžืงื•ืจ:

{... IS NULL} OR
{... ะšะพะผะฟะปะตะบั‚} OR
{... ะ”ะพะบัƒะผะตะฝั‚ะŸะพะ—ะฐั€ะฟะปะฐั‚ะต} AND
( {... ะฝะตั€ะฐะฒะตะฝัั‚ะฒะฐ} )

ืื•ืคืก... ืœืžืขืฉื” ื”ืื˜ ื–ื™ืš ืืจื•ื™ืกื’ืขืฉื˜ืขืœื˜ ืื– ืื•ื™ื‘ ืื™ื™ื ืขืจ ืคื•ืŸ ื“ื™ ืขืจืฉื˜ืข ืฆื•ื•ื™ื™ ื‘ืื“ื™ื ื’ื•ื ื’ืขืŸ ืื™ื– ืืžืช, ื•ื•ืขืจื˜ ื“ื™ ื’ืื ืฆืข ืžืฆื‘ TRUE, ืึธืŸ ื’ืขื ื•ืžืขืŸ ืื™ืŸ ื—ืฉื‘ื•ืŸ ื™ื ืึทืงื•ื•ืึทืœืึทื˜ื™ื–. ืื•ืŸ ื“ืึธืก ืื™ื– ื’ืึธืจ ื ื™ื˜ ื•ื•ืึธืก ืžื™ืจ ื’ืขื•ื•ืืœื˜.

ื–ืืœ ืก ืคืึทืจืจื™ื›ื˜ืŸ ืขืก:

(
  {... IS NULL} OR
  {... ะšะพะผะฟะปะตะบั‚} OR
  {... ะ”ะพะบัƒะผะตะฝั‚ะŸะพะ—ะฐั€ะฟะปะฐั‚ะต}
) AND
( {... ะฝะตั€ะฐะฒะตะฝัั‚ะฒะฐ} )

ืคึผืจืึธื‘ืœืขื #4 (ืงืœื™ื™ืŸ): ืงืึธืžืคึผืœืขืงืก ืึธื“ืขืจ ืฆื•ืฉื˜ืึทื ื“ ืคึฟืึทืจ ืื™ื™ืŸ ืคืขืœื“

ืึทืงื˜ื•ืึทืœืœื™, ืžื™ืจ ื”ืึธื‘ืŸ ืคึผืจืึธื‘ืœืขืžืก ืื™ืŸ ื ื•ืžืขืจ 3 ื“ื•ื•ืงื ื•ื•ื™ื™ึทืœ ืขืก ื–ืขื ืขืŸ ื’ืขื•ื•ืขืŸ ื“ืจื™ื™ึท ื‘ืื“ื™ื ื’ื•ื ื’ืขืŸ. ืื‘ืขืจ ืึทื ืฉื˜ืึธื˜ ืคื•ืŸ ื–ื™ื™ ืื™ืจ ืงืขื ืขืŸ ื‘ืึทืงื•ืžืขืŸ ื“ื•ืจืš ืžื™ื˜ ืื™ื™ืŸ, ื ื™ืฆืŸ ื“ื™ ืžืขืงืึทื ื™ื–ืึทื coalesce ... IN:

coalesce(NEW."ะ”ะพะบัƒะผะตะฝั‚_"::text, '') IN ('', '"ะšะพะผะฟะปะตะบั‚"', '"ะ”ะพะบัƒะผะตะฝั‚ะŸะพะ—ะฐั€ะฟะปะฐั‚ะต"')

ืึทื–ื•ื™ ืžื™ืจ NULL "ืžื™ืจ ื•ื•ืขืœืŸ ื›ืึทืคึผืŸ", ืื•ืŸ ืฉื•ื•ืขืจ OR ืขืก ืื™ื– ื ื™ื˜ ื“ืึทืจืคึฟืŸ ืฆื• ืคึผืœื•ื™ื˜ ืžื™ื˜ ื‘ืจืึทืงืึทืฅ.

ืื™ืŸ ื’ืึทื ืฅ

ืœืึธืžื™ืจ ืจืขืงืึธืจื“ื™ืจืŸ ื•ื•ืึธืก ืžื™ืจ ื”ืึธื‘ืŸ:

IF (
  coalesce(NEW."ะ”ะพะบัƒะผะตะฝั‚_"::text, '') IN ('', '"ะšะพะผะฟะปะตะบั‚"', '"ะ”ะพะบัƒะผะตะฝั‚ะŸะพะ—ะฐั€ะฟะปะฐั‚ะต"') AND
  (
    OLD."ะ”ะพะบัƒะผะตะฝั‚ะะฐัˆะฐะžั€ะณะฐะฝะธะทะฐั†ะธั"
  , OLD."ะฃะดะฐะปะตะฝ"
  , OLD."ะ”ะฐั‚ะฐ"
  , OLD."ะ’ั€ะตะผั"
  , OLD."ะ›ะธั†ะพะกะพะทะดะฐะป"
  ) IS DISTINCT FROM (
    NEW."ะ”ะพะบัƒะผะตะฝั‚ะะฐัˆะฐะžั€ะณะฐะฝะธะทะฐั†ะธั"
  , NEW."ะฃะดะฐะปะตะฝ"
  , NEW."ะ”ะฐั‚ะฐ"
  , NEW."ะ’ั€ะตะผั"
  , NEW."ะ›ะธั†ะพะกะพะทะดะฐะป"
  )
) THEN ...

ืื•ืŸ ืื•ื™ื‘ ืื™ืจ ื‘ืึทื˜ืจืึทื›ื˜ืŸ ืึทื– ื“ื™ ืฆื™ื ื’ืœ ืคื•ื ืงืฆื™ืข ืงืขื ืขืŸ ื–ื™ื™ืŸ ื’ืขื•ื•ื™ื™ื ื˜ ื‘ืœื•ื™ื– ืื™ืŸ UPDATE-ืฆื™ื ื’ืœ ืจืขื›ื˜ ืฆื• ืึทื•ื•ื™ื™ืœืึทื‘ื™ืœืึทื˜ื™ OLD/NEW ืื™ืŸ ื“ืขืจ ืื•ื™ื‘ืขืจืฉื˜ืขืจ ืžื“ืจื’ื” ืฆื•ืฉื˜ืึทื ื“, ื“ืขืžืึธืœื˜ ื“ืขื ืฆื•ืฉื˜ืึทื ื“ ืงืขื ืขืŸ ื‘ื›ืœืœ ื–ื™ื™ืŸ ื’ืขืฉื˜ืขืœื˜ ืื™ืŸ WHEN- ืฆื•ืฉื˜ืึทื ื“, ื•ื•ื™ ื’ืขื•ื•ื™ื–ืŸ ืื™ืŸ # 1 ...

ืžืงื•ืจ: www.habr.com

ืœื™ื™ื’ืŸ ืึท ื‘ืึทืžืขืจืงื•ื ื’