Assuming I have the following function being executed on a row-update:
CREATE OR REPLACE FUNCTION versioned_update()
RETURNS TRIGGER AS $$
DECLARE
sql TEXT;
BEGIN
sql := 'INSERT INTO backup_table VALUES (OLD)';
EXECUTE sql;
END;
$$ language 'plpgsql';
The above example does not work because OLD
is unknown in that execution context. So I tried something like:
sql := format('INSERT INTO backup_table VALUES (%L)', OLD);
and
sql := format('INSERT INTO backup_table VALUES (%L)', (OLD));
and
sql := format('INSERT INTO backup_table VALUES (%L)', (OLD.*));
All without any luck.
This issue is part of a larger trigger I am working on. Looked in isolation it does not make much sense, but illustrates the question.
Best Answer
After a lot of trial and error I've finally figured it out. The trick was the notation for composite data types (and more importantly their usage) which eventually led me to this: