From: | Jeff Boes <jeff(at)endpoint(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Reference new.* or old.* in dynamic statement? |
Date: | 2005-08-09 14:45:52 |
Message-ID: | 42F8C1A0.3070200@endpoint.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'm trying to write a general-purpose trigger that will disallow updates
on certain fields (I could probably do this in other ways, but I have a
stubborn streak ...).
Given a table, I want to define a trigger on that table that will
"write-protect" one column by name:
CREATE TRIGGER tbl_nomod_create
BEFORE INSERT OR UPDATE ON tbl
FOR EACH ROW EXECUTE PROCEDURE
no_modification_allowed('create_date');
I.e.,
UPDATE tbl SET fld_1 = 'foo;
would be OK but
UPDATE tbl SET create_date = now();
would result in an exception.
My trigger function below attempts to create a dynamic SQL statement
that tests "old.<column-name>" against "new.<column-name>".
CREATE OR REPLACE FUNCTION no_modification_allowed()
RETURNS TRIGGER
LANGUAGE 'plpgsql'
AS '
DECLARE
tmp_stmt TEXT;
result RECORD;
BEGIN
IF TG_ARGV[0] IS NULL
THEN
RETURN new;
ELSE
tmp_stmt := ''SELECT 1 AS is_null FROM (SELECT 1) AS dual WHERE '';
FOR result IN EXECUTE (tmp_stmt
|| ''old.'' || quote_ident(TG_ARGV[0])
|| '' IS NULL'') LOOP
RETURN new;
END LOOP;
FOR result IN EXECUTE (tmp_stmt
|| ''old.'' || quote_ident(TG_ARGV[0])
|| '' = new.''
|| quote_ident(TG_ARGV[0])) LOOP
RETURN new;
END LOOP;
RAISE EXCEPTION ''Cannot modify % in %'', TG_ARGV[0], TG_RELNAME;
END IF;
END
';
I tried one or two other approaches in the dynamic statement, but
generally I get errors indicating that "new" and "old" can't be
referenced in this fashion:
ERROR: OLD used in query that is not in a rule
Is there a way to do what I want?
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2005-08-09 14:47:56 | Re: Cross database queries |
Previous Message | Sean Davis | 2005-08-09 14:41:37 | Re: Cross database queries |