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 · Accepted Answer · 2015-05-17 14:14:36Z

3

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.

answered May 17, 2015 at 14:14

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3277403 Over a year ago

Thanks, I have an autoincrement ID field and I haven't included.

percy · Accepted Answer · 2015-05-17 10:26:22Z

0

You are passing four values for three placeholders. I presume that "audit" should be a placeholder for TG_TABLE_NAME?

answered May 17, 2015 at 10:26

percy

1,12811 silver badges9 bronze badges

1 Comment

user3277403 Over a year ago

Silly mistake on my behalf. Updated the insert to have three values; however I get this error. ERROR: invalid input syntax for integer: "covenant" Where: PL/pgSQL function audit() line 3 at EXECUTE statement

Collectives™ on Stack Overflow

Invalid input syntax PLPGSQL script

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related