Thread: field names for new in trigger function

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.

Hi.

On Monday 03 May 2010 at 18:03 Charles Holleran wrote:

[snip]

> Can't change the column names now.  What is correct syntax to get the
>  "sourceDate" field of the NEW object?
>
> Any insight appreciated.

I don't know about in code, but in straight SQL column names with upper-case
need to be quoted. Might be worth a try.

Cheers,

Mark

Have you tried quoting the field name, you're using a mixed case field name and AFAIK, if you don't quote PostgresSQL looks for the field in lowercase, only quoting preserves the case as you want it.

Again AFAIK

Didier