Thread: Using OLD on INSERT

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/

We check the value of TG_OP as in:
IF TG_OP = ''UPDATE'' THEN
    Code that address OLD and NEW here;
ELSE
    Code that addresses only NEW here.
END IF;

On Thursday 22 January 2004 08:29 am, Paul Makepeace wrote:
> 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)

--
Quote: 8
"Even now politicians use the phrase 'federal dollars' as a synonym for
 'free money.' It's a dangerous tendency, for it leaves the states
 accountable to Washington rather than to their own voters. This mix of
 state and federal governments is not just economically suspect but
 politically corrosive; it undermines the essence of real federalism:
 the integrity of both state and federal governments. If we're serious
 about states' rights, then we need to get serious about states'
 responsibilities. The two go together. As with love and marriage, you
 can't have one without the other."

 --Paul Greenberg

 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com

After looking at this again, I see that my answer doesn't help you. We use the
same trigger code on INSERT OR UPDATE. According to the online docs I have:
OLD
 Data type RECORD; variable holding the old database row for UPDATE/DELETE
operations in ROW level triggers.

OLD is only available for UPDATE/DELETE. As for workarounds, I don't know of
any.

On Thursday 22 January 2004 09:26 am, Terry Lee Tucker wrote:
> We check the value of TG_OP as in:
> IF TG_OP = ''UPDATE'' THEN
>     Code that address OLD and NEW here;
> ELSE
>     Code that addresses only NEW here.
> END IF;
>
> On Thursday 22 January 2004 08:29 am, Paul Makepeace wrote:
> > 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)

--
Quote: 36
"War is an ugly thing, but not the ugliest of things. The person who
 has nothing for which he is willing to fight, nothing which is more
 important than his own personal safety, is a miserable creature and
 has no chance of being free unless made and kept so by the exertions
 of better men than himself."

 --John Stuart Mill

 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com

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