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

field names for new in trigger function

From: Charles Holleran <scorpdaddy(at)hotmail(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: field names for new in trigger function
Date: 2010-05-03 17:03:53
Message-ID: BAY126-W11FD8970EAD2EAFBA452EFCEF20@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-novice
Have a table
 
CREATE TABLE transaction
(
  "sourceDate" timestamp with time zone,
  "sourceName" character varying(300),
  uid_commission integer,
  attempts integer
)
 
Have another table
 
CREATE TABLE freshness
(
  uid_commission integer,
  "sourceDate" timestamp with time zone
);
 
Have a trigger
 
CREATE TRIGGER update_freshness
  AFTER INSERT OR UPDATE
  ON transaction
  FOR EACH ROW
  EXECUTE PROCEDURE update_freshness();
 
Finally have procedure
 
CREATE OR REPLACE FUNCTION update_freshness()
  RETURNS trigger AS $ $
DECLARE
  latest timestamp with time zone;
BEGIN
 
  SELECT sourceDate INTO latest FROM freshness WHERE uid_commission = NEW.uid_commission;
  
  IF FOUND THEN
    IF NEW.sourceDate > latest THEN
       UPDATE freshness SET sourceDate = NEW.sourceDate WHERE uid_commission = NEW.uid_commission;
    END IF;
  ELSE
    INSERT INTO freshness (uid_commission, sourceDate) VALUES (NEW.uid_commission, NEW.sourceDate);
  END IF;
  
  RETURN NULL;
 
END;  $$ LANGUAGE 'plpgsql';
 
 
The trouble is the column names.  NEW.sourceDate pukes.  Error says, 'column "sourcedate" does not exist';
 
Can't change the column names now.  What is correct syntax to get the "sourceDate" field of the NEW object?
 
Any insight appreciated.
 
New_To_Sql_Guy
 
  		 	   		  
_________________________________________________________________
Videos that have everyone talking! Now also in HD!
http://go.microsoft.com/?linkid=9724465

Responses

pgsql-novice by date

Next:From: Didier Gasser-MorlayDate: 2010-05-04 06:31:32
Subject: Re: Last modification date for Triggers, functions, tables ....
Previous:From: Tom LaneDate: 2010-05-01 22:56:07
Subject: Re: returning more than one value from a function

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