I am trying to write a dynamic trigger function on insert operations so that the same function can be used across all my tables. In this case my tables are ‘test’ and ‘test_a’ (my convention is that all audit table names are the name of the original table concatenated with ‘_a’). Below is part of my code in plpgsql:
CREATE OR REPLACE FUNCTION audit_insert()
RETURNS "trigger" AS
$BODY$
DECLARE
new_audit_row RECORD;
dynamic_SQL text;
BEGIN
--Instantiate new_audit_row to the required type.
dynamic_SQL := 'SELECT INTO new_audit_row * ' ||
'FROM ' || quote_ident(TG_RELNAME || '_a') || ';';
EXECUTE dynamic_SQL;
--... more code here
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
However, when I try to insert data in ‘test’ I am getting the following error message:
ERROR: syntax error at or near "INTO" at character 8
QUERY: SELECT INTO new_audit_row * FROM "test_a";
CONTEXT: PL/pgSQL function "audit_insert" line 18 at execute statement
The funny thing is that the documentation I read about SELECT INTO and RECORD types give the following example, amongst others:
DECLARE
users_rec RECORD;
BEGIN
SELECT INTO users_rec * FROM users WHERE user_id=3;
--...more code
END;
(full code can be found at http://www.postgresql.org/docs/8.0/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT )
Which is basically identical (except for the WHERE clause) to the query returned in the my error message!!
Can anyone help please?
Regs,
Mark J Camilleri
