1

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.

3

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:

CREATE OR REPLACE FUNCTION versioned_update()
RETURNS TRIGGER AS $$
DECLARE
     sql TEXT;
BEGIN
    sql := 'INSERT INTO backup_table VALUES $1.*';
    EXECUTE sql USING OLD;
END;
$$ language 'plpgsql';
  • I'm aware of that. But I have to wait a day until I am allowed to do that ;) I actually just arrived here to do just that ;) – exhuma Sep 19 '18 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.