Skip site navigation (1) Skip section navigation (2)

Re: BUG #4997: Expression evaluation rules

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Dmitry Samokhin <sdld(at)mail(dot)ru>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4997: Expression evaluation rules
Date: 2009-08-20 09:42:06
Message-ID: 162867790908200242q3ba9fccfu8c6475b29821ded8@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Hello

it's not bug, but feature.


>
> CREATE OR REPLACE FUNCTION trg_mytable_after()
>  RETURNS trigger AS
> $BODY$
> BEGIN
>  IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
>    IF (CASE WHEN TG_OP = 'DELETE' THEN TRUE ELSE OLD.a <> NEW.a END) THEN
>      RAISE NOTICE 'OK!';
>    END IF;
>  END IF;
>

CASE expression isn't evaluated internally in plpgsql, but it is
transformed to SQL statement, that is evaluated by SQL procession. You
code is some like:

DECLARE result boolean;
BEGIN
   IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
     EXECUTE 'CASE WHEN $1 = 'DELETE' THEN true ELSE $2.a <> $3.a END'
USING TG_OP, OLD.a, OLD.b INTO result;
    IF result THEN
      RAISE NOTICE 'OK!';

Regards
Pavel Stehule

In response to

Responses

pgsql-bugs by date

Next:From: Dmitry SamokhinDate: 2009-08-20 14:27:54
Subject: Re: BUG #4997: Expression evaluation rules
Previous:From: Pavel StehuleDate: 2009-08-20 09:33:15
Subject: Re: BUG #4997: Expression evaluation rules

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group