Postgres: pass schema as variable to a function with trigger-CodePudding

I'd pass as variable the schema name in a function with trigger

Function and trigger are so defined:

CREATE OR REPLACE FUNCTION test()  RETURNS TRIGGER AS $shp_create$

  BEGIN
    RAISE NOTICE 'NEW: %', NEW;
    IF NEW.log_type = false THEN

        INSERT INTO target_table
        SELECT * FROM $%%NEW.schema_or%%%.start_table 
    
        NEW.log_type := true;

      RETURN NEW;
    END IF;
    RETURN NEW;
  END;

 $test$ LANGUAGE plpgsql;


DROP TRIGGER IF EXISTS test_trigger ON table_log;
CREATE TRIGGER test_trigger BEFORE INSERT ON table_log
  FOR EACH ROW EXECUTE PROCEDURE test();

table_log is so defined:

log_type|schema_or
--------|---------
 false    test

So the Insert statment in the function become:

INSERT INTO target_table
SELECT * FROM test.start_table

CodePudding user response：

Per Dynamic query:

EXECUTE format('INSERT INTO target_table
        SELECT * FROM %I.start_table', NEW.schema_or )

This use the string function format to create a query string that can then be executed. Look at the format link for a full explanation of what is possible. In this case %I refers to an identifier, the schema name.