Thanks to Ed Loehr and others on the group, I finally was able to
create triggers in my database. This is a summary of what I learned
in the process. For the most part, I didn't find this in the
documentation. If anything here is incorrect, please let me know.
If not, can it be put in documentation somewhere? or in the FAQ?
- The actual working code for a trigger must be put into a function
which is called by the trigger. [This *is* in the docs]
- If the trigger function needs access to rows which are affected by
the insert/update/delete, the trigger function must use plpgsql as a
language. A sql function cannot access the special 'old' and 'new'
rows.
- Before creating a function in plpgsql, a handler and trusted
language must be created. Example syntax:
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
'/install/lib/path/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
- The return type for a plpgsql function must be opaque.
- A value must be returned if a return type is specified. The old &
new records are available as return values from the plpgsql
function.
- The body of a plpgsql function looks like sql except for reference
to old and new. The SQL part of the function must be enclosed with
'begin' and 'end;' or there will be a compiler error at run time.
- Example triggers and plpgsql functions can be found in the
postgres subdirectory: src/test/regress/sql.
- If a trigger function is dropped and recreated, the corresponding
trigger must also be dropped and recreated. Otherwise postgres
6.5.3 will give a runtime error that the cache lookup failed.