Postgres: checking value before conditionally running an update or delete

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 fie开发者_高级运维ld 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!

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

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;