Not able to access the Primary Key value in PostgreSQL Trigger Function

Question

I am just started writing the PL/pgSQL Trigger function. I am having couple of tables called Student and Result. Student having the following columns. ID,name,subject,mark (ID is the primary key) and the Result table is having two columns like ID,Status

Whenever one record has added in the student table, I want to update the Result table by checking the mark in the Student table, If the mark entered is greater than 50 then one record should be inserted in the Result table with ID and Status = Pass and if it is less than 50 then status will be fail. I have the following Trigger function to achieve this

CREATE OR REPLACE FUNCTION "UpdateResult"() RETURNS trigger AS $BODY$
  BEGIN  
    IF NEW.mark < 50 THEN   
       INSERT INTO "Result" SELECT 92,'fail';
       RETURN NEW;
  ELSE 
 INSERT INTO "Result" SELECT 92,'pass';
 RETURN NEW;  
END IF;      
     END; 
    $BODY$
      LANGUAGE 'plpgsql' VOLATILE STRICT SECURITY DEFINER
      COST 100;
     ALTER FUNCTION "UpdateResult"() OWNER TO postgres;

CREATE TRIGGER "Result"
  AFTER INSERT
  ON "Student"
  FOR EACH ROW
  EXECUTE PROCEDURE "UpdateResult"();

By this function trigger has worked as expected since I have hard coded the primary key value. But When I modify the SQL inside Trigger function like the following

INSERT INTO "Result" SELECT NEW.ID,'fail'; (or) INSERT INTO "Result" SELECT NEW.ID,'pass';

It is throwing error like

>  ***Record "new" has no field "id"   Context : PL/pgSQL function
> "UpdateResult" line 3 at SQL statement***

Means it is able to take the values of non primary key values from NEW variable not the primary key value. Can any one please tell me is there a restriction in PL/pgSQL or Am I doing anything wrong !

Check the capitalisation of the "ID" field - if it's in capitals then you need to use it the same way (by quoting it): INSERT INTO "Result" SELECT NEW."ID",NEW.name; — Milen A. Radev
Hi Milen., Yes it is in capital letter , after adding quotes it's working, can you please brief me why we need to do like this — Murugesh

Christian Christian · Accepted Answer · 2012-06-04T17:31:56

Just a hint: why are you using quoted names? When doing this, you have to care about capitalisation.

See if this works:

CREATE OR REPLACE FUNCTION UpdateResult() RETURNS trigger AS $BODY$
  BEGIN  
    IF NEW.mark < 50 THEN   
       INSERT INTO result (id, status) values (92,'fail');
       RETURN NEW;
  ELSE 
       INSERT INTO result (id, status) values (92,'pass');
       RETURN NEW;  
END IF;      
     END; 
    $BODY$
      LANGUAGE 'plpgsql' VOLATILE STRICT SECURITY DEFINER
      COST 100;
     ALTER FUNCTION UpdateResult() OWNER TO postgres;

CREATE TRIGGER Result
  AFTER INSERT
  ON Student
  FOR EACH ROW
  EXECUTE PROCEDURE UpdateResult();

Not able to access the Primary Key value in PostgreSQL Trigger Function

1 Answers