Invalid input syntax PLPGSQL script

Question

I have a trigger function that is going to audit transactions made on a table.

CREATE or REPLACE FUNCTION audit()
  returns trigger
  language plpgsql
as $$
begin
EXECUTE FORMAT ('INSERT INTO %I.audit VALUES (%L,%L,%L)',TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP, now());
 RETURN NEW;
end;
$$

However I keep getting "ERROR: invalid input syntax for integer: "triggerTable" Where: PL/pgSQL function audit() line 3 at EXECUTE statement" (Where trigger table is the table that has triggered this function)

Is the I assume the syntax of my execute query is wrong, but I can't isolate where. Any feedback would be welcome

Pavel Stehule Pavel Stehule · Accepted Answer · 2015-05-17T14:14:36

The problem is in casting to target table. Some target column is integer, but you push text there. It is same error like this:

postgres=# create table foo(a int);
CREATE TABLE

postgres=# insert into foo values('AHOJ');
ERROR:  invalid input syntax for integer: "AHOJ"
LINE 1: insert into foo values('AHOJ');
                               ^

It is looking like first column of audit table is integer (probably id), but you push table name there.

Invalid input syntax PLPGSQL script

2 Answers