Thread: Reference new.* or old.* in dynamic statement?

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?

Jeff Boes wrote:
> 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');

> CREATE OR REPLACE FUNCTION no_modification_allowed()
>  RETURNS TRIGGER
>  LANGUAGE 'plpgsql'
>  AS '

It's a lot easier if you use TCL/Perl/one of the other interpreted
languages. Bound to be an example in the mailing list archives, I might
even have posted one.

--
   Richard Huxton
   Archonet Ltd

Jeff Boes <jeff@endpoint.com> writes:
> 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 ...).

I think it's pretty much impossible to do this in plpgsql.  You could do
it in the other PLs that support triggers, or in C.  From a performance
standpoint I'd think you'd want to do it in C anyway.  There are some
closely related example trigger functions in the contrib tree.

            regards, tom lane