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!
MSN.ca Video.
