Date: Tue, 4 Nov 2003 17:33:19 -0500
From: Jamie Lawrence <postgres@jal.org>
To: pgsql-sql@postgresql.org
Subject: Problems with NEW.* in triggers
Message-ID: <20031104223319.GA2879@clueinc.net>
Mail-Followup-To: pgsql-sql@postgresql.org
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.3.28i
Organization: clue inc


Hi folks -

I'm having a problem with what looks like it should be trivial.

For the function

create or replace function timestamp_fn() returns opaque as '
        begin
        NEW.moddate := coalesce(NEW.moddate, now());
        return NEW;
        end
' language 'plpgsql';

on an after insert or update trigger, never sets moddate to now().

I had thought that if moddate isn't included in an insert or update,
that it would be null in the NEW context, so that this would fall 
through to the now() call. (I also tried this as below:

create or replace function timestamp_fn() returns opaque as '
     begin
     if NEW.moddate is not null then
             return NEW;
     else
             NEW.moddate := now();
             return NEW;
     end if;
      end
' language 'plpgsql';

With the same results.)

Any thoughts on what I'm doing wrong??

-j


-- 
Jamie Lawrence                                        jal@jal.org
Never eat anything bigger than your head.