Postgresql [42P01] ERROR: relation does not exist during trigger function

Question

I am trying to write a trigger that stores previous versions of a row in a table named audit_tablename given a table named tablename.

Here is the the code...

CREATE OR REPLACE FUNCTION process_ui_audit()
RETURNS TRIGGER AS
$$
DECLARE

    audit_table_name   text := TG_TABLE_SCHEMA || '.audit_' || TG_TABLE_NAME;
    audit_table_schema text := TG_TABLE_SCHEMA;

BEGIN

    IF (TG_OP = 'UPDATE')
    THEN

        EXECUTE FORMAT('INSERT INTO %1$I SELECT NEXTVAL(''$1.hibernate_sequence''),now(), user, ($1).*',
                       audit_table_name, audit_table_schema)
            USING OLD;

        NEW.version = OLD.version + 1;

        RETURN NEW;
    ELSIF (TG_OP = 'INSERT')
    THEN
        NEW.version = 1;
        RETURN NEW;
    END IF;
END;

When I try to update a row the trigger runs and I get errors like this....

[42P01] ERROR: relation "webapp.audit_portal_user" does not exist
Where: PL/pgSQL function webapp.process_ui_audit() line 13 at EXECUTE

I am wonderin am I formatting table names incorrectly or something? The table name webapp.audit_portal_user definetly exists.

It seems a case(upper/lower/mixed case) problem with your table/Schema/Column name. share your table structure — Akhilesh Mishra
Error Code 42P01 denotes invalid table error. if your table name is not in lower case then use correct case of table name and put it in double quotes. — Akhilesh Mishra

pifor pifor · Accepted Answer · 2020-06-01T08:23:51

It works without specifying schema name.

Here is a simplified example:

create table portal_user(
uid     int,
uname   text
);
CREATE TABLE

create table audit_portal_user(
uid      int,
uname    text,
who      text,
what     text,
ts       timestamp
);
CREATE TABLE

create or replace function process_ui_audit()
returns trigger as
$$
declare
audit_table_name   text := 'audit_' || tg_table_name;
begin
 if (tg_op = 'UPDATE')
 then
    execute format('insert into %I values($1.*, user, %L, now())', 
                    audit_table_name, 'UPDATE') using new;
    return null;
 end if;
end;
$$
language plpgsql;
CREATE FUNCTION

create trigger audit
after update on portal_user
for each row
execute function process_ui_audit();
CREATE TRIGGER

insert into portal_user values(12, 'titi');
INSERT 0 1

select * from portal_user;
 uid | uname 
-----+-------
  12 | titi
(1 row)

update portal_user set uname='toto' where uid=12;
UPDATE 1

select * from portal_user;
 uid | uname 
-----+-------
  12 | toto
(1 row)

select * from audit_portal_user;
 uid | uname |   who    |  what  |             ts             
-----+-------+----------+--------+----------------------------
  12 | toto  | postgres | UPDATE | 2020-06-01 10:20:36.549257
(1 row)

Postgresql [42P01] ERROR: relation does not exist during trigger function

1 Answers