field names for new in trigger function

Поиск
Список
Период
Сортировка
От Charles Holleran
Тема field names for new in trigger function
Дата
Msg-id BAY126-W11FD8970EAD2EAFBA452EFCEF20@phx.gbl
обсуждение исходный текст
Ответы Re: field names for new in trigger function  (Mark Kelly <pgsql@wastedtimes.net>)
Список 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! MSN.ca Video.

В списке pgsql-novice по дате отправления:

Предыдущее
От: Mladen Gogala
Дата:
Сообщение: Re: how to continue after error in batch mode with psql
Следующее
От: Jaime Casanova
Дата:
Сообщение: Re: Autovaccum and Full vacuum