Re: Using OLD on INSERT - Mailing list pgsql-novice

From Marcus Andree S. Magalhaes
Subject Re: Using OLD on INSERT
Date
Msg-id 65010.200.174.148.100.1074785157.squirrel@webmail.webnow.com.br
Whole thread Raw
In response to Using OLD on INSERT  (Paul Makepeace <postgresql.org@paulm.com>)
List pgsql-novice
I can't quite understand the problem. There are minor differences
between insert triggers and update triggers in postgresql...
AFAIK the values OLD and NEW are related to the data being
inserted/updated/modified right now and can't imagine why someone
would use 'old' in a simple insert statement, but here goes my
humble opinions:

 1 - make two triggers. One for insertin and another for updating
 2 - short-circuit OR can be "simulated" using if-then-else clauses:
    if new.expires is null then
       new.expires = //whatever//
    else
      if new.expires = old.expires then
        new.expires = //whatever//
      end if;
    end if;

If you want to be sure that a column is being modified, write a
update trigger.

Hope this helps a bit.

> I have a trigger that sets an expires column to
> last_access+expiry::interval if expires IS NULL or if the expires value
> isn't being set or changed.
>
>   IF NEW.expires IS NULL OR NEW.expires = OLD.expires THEN
>       NEW.expires = NEW.last_access+NEW.expiry:interval;
>   END IF;
>
> The problem here is OLD doesn't exist on the first INSERT which throws
> an error. It seems PL/pgSQL doesn't have C's short-circuit booleans.
>
> a) Is there a way around this?
> b) is there a 'right' way to determine if a column is being changed?
>
> Paul (total PL/pgSQL newbie)
>
> --
> Paul Makepeace ................................
> http://paulm.com/ecademy
>
> "If I had new shoes, then he wouldn't sing Halleighluha."
>    -- http://paulm.com/toys/surrealism/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly




pgsql-novice by date:

Previous
From: Terry Lee Tucker
Date:
Subject: Re: Using OLD on INSERT
Next
From: Tom Lane
Date:
Subject: Re: executing backup remotly