Postgres: checking value before conditionally running an update or delete

Question

I've got a fairly simple table which stores the records' authors in a text field as shown here:

CREATE TABLE "public"."test_tbl" (
  "index" SERIAL, 
  "testdate" DATE, 
  "pfr_author" TEXT DEFAULT "current_user"(), 
  CONSTRAINT "test_tbl_pkey" PRIMARY KEY("index");

The user will never see the index or pfr_author fields, but I'd like them to be able to UPDATE the testdate field or DELETE whole records if they have permission and if they are the author. i.e. if test_tbl.pfr_author = CURRENT_USER THEN permit the UPDATE OR DELETE, but if not then raise an error message such as "Sorry, you do not have permission to edit this record.".

I have not gone down the route of using a trigger as I figure that even if it is executed before row update the user-requested update will still take place afterwards regardless.

I've tried doing this through a rule, but end up with infinite recursion as I put an update command inside the rule. Is there some way to do this using rules alone or a combination of a rule and trigger?

Thanks very much for any help!

Answer 1

Use a row level BEFORE trigger on UPDATE and DELETE to do this. Just have it return NULL when the operation is not permitted and the operation will be skipped.

http://www.postgresql.org/docs/9.0/interactive/trigger-definition.html

Answer 2

the trigger function have some problem,resulting recursive loop update.You should do like this:

CREATE OR REPLACE FUNCTION "public"."test_tbl_trig_func" () RETURNS trigger AS $body$ 
BEGIN 
IF not (old.pfr_author = "current_user"() OR "current_user"() = 'postgres') THEN 
    NULL;  
END IF; 
RETURN new;
END; 
$body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 100; 

I have a test like this,it does well;

UPDATE test_tbl SET testdate = CURRENT_DATE WHERE test_tbl."index" = 2;