Postgres: pass schema as variable to a function with trigger

Question

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

score 2 · Accepted Answer · 2021-11-25 16:22:23Z

2

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.

edited Nov 25, 2021 at 16:22

answered Nov 25, 2021 at 16:15

user7070613

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

1 Comment

user7070613 Over a year ago

Not sure why: format('INSERT INTO target_table SELECT string_agg(field::varchar,''-'') FROM %I.start_table', 'test' ); yielding: INSERT INTO target_table SELECT string_agg(field::varchar,'-') FROM test.start_table would not work? What is the error you get?

Collectives™ on Stack Overflow

Postgres: pass schema as variable to a function with trigger

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related