Error: Relation "abc.emp_audit" does not exist. when i add schema name in all objects. it was working fine without schema

user_10062703 0
Viewed 153
 CREATE TABLE abc.emp
         (
                      empname TEXT NOT NULL,
                      salary INTEGER
         );

 CREATE TABLE abc.emp_audit
         (
                      operation CHAR(1) NOT NULL,
                      stamp TIMESTAMP NOT NULL,
                      userid TEXT NOT NULL,
                      empname TEXT NOT NULL,
                      salary INTEGER
         );

      CREATE FUNCTION abc.audit()
     returns TRIGGER AS $$ ...
    EXECUTE format
   ( 'INSERT INTO %I SELECT ''I'', current_timestamp, %L, ($1::%I.%I).*', 
   tg_argv[0], CURRENT_USER, tg_table_schema, tg_table_name ) using new;
  ... $$;

 CREATE TRIGGER emp_audit_trig after
 INSERT
 OR
UPDATE
OR
DELETE
ON abc.emp FOR each row
EXECUTE FUNCTION
abc.audit('abc.emp_audit');

I got error : Relation "abc.emp_audit" does not exist . How to correct this issue when schema added in tables ad audit tables, functions, and triggers arguments.

triggers postgresql sql
2 Answers

You'd have to use two parameters, one for the schema and one for the table. In the trigger function, use

EXECUTE
   format(
      'INSERT INTO %I.%I SELECT ''I'', current_timestamp, %L, ($1::%I.%I).*', 
      TG_ARGV[0], TG_ARGV[1],
      current_user,
      TG_TABLE SCHEMA, TG_TABLE NAME
   ) USING NEW;

Then define the trigger as

CREATE TRIGGER ... EXECUTE FUNCTION abc.audit('abc', 'emp_audit');
user_64643080

An alternative to the one proposed by @Laurenz Albe is to stick to a single parameter and then split it before creating the INSERT statement, e.g

CREATE FUNCTION abc.audit() RETURNS TRIGGER AS $$ 
DECLARE 
  tbname TEXT := (string_to_array(tg_argv[0],'.'))[1];
  schname TEXT := (string_to_array(tg_argv[0],'.'))[2];
BEGIN
-- rest of your code ...
EXECUTE format('INSERT INTO %I.%I SELECT ''I'', current_timestamp, %L, ($1::%I.%I).*',
         tbname,schname,CURRENT_USER,tg_table_schema,tg_table_name ) USING NEW;
-- rest of your code ...
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit_trig AFTER INSERT OR UPDATE OR DELETE
ON abc.emp FOR EACH ROW EXECUTE FUNCTION abc.audit('abc.emp_audit');

Demo: db<>fiddle

user_22753880
Related