Re: [GENERAL] sub query constraint

[Dale Sykora]

Yudie Pg wrote:
CREATE OR REPLACE FUNCTION validate_actions_insert() RETRUNS OPAQUE AS '
CREATE OR REPLACE FUNCTION validate_actions_insert() RETURNS OPAQUE AS $$
DECLARE
rs RECORD;
BEGIN
  SELECT INTO rs * FROM user_data WHERE name = NEW.user and write_access = 't';
   
  IF NOT FOUND THEN
     RAISE EXCEPTION ''writing access forbidden for user '', NEW.user;
      RAISE EXCEPTION 'writing access forbidden for user %', NEW.user;
  END IF;
  RETURN NEW;
END;
' LANGUAGE plpgsql;
 $$ LANGUAGE plpgsql;
CREATE TRIGGER tg_actions BEFORE INSERT OR UPDATE ON actions
FOR EACH ROW EXECUTE PROCEDURE validate_actions_insert();
Yugi,
	I made a few minor modifications as shown above and the trigger 
function works great.  I think I'll also use triggers to keep a history 
of record changes for auditing.

Thanks,
Dale
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match