Pass Parameters to trigger function to execute dynamic SQL

Question

I have this code which was solved by Erwin:
Execute dynamic INSERT ON CONFLICT DO UPDATE in plpgsql function

Now I want the trigger to call the trigger function insert_data_func with parameters (model_cd, processdate).

There are different models, that's why I'm trying to simplify things by using dynamic SQL.

Function:

CREATE OR REPLACE FUNCTION insert_data_func() 
RETURNS TRIGGER AS 
$func$ 
DECLARE 
 model_cd text;
 processdate text;
BEGIN 
 model_cd := TG_ARGV[0];
 processdate := TG_ARGV[1];
 EXECUTE
 $x$INSERT INTO tb_moldsummary AS t
 SELECT 1ドル.machine 
 , 1ドル.model
 , split_part(lot, '_', 1)
 , right(lot, position('_' IN lot) * -1) 
 , COUNT(lot)
 , 1ドル.datetimestamp
 FROM model$x$ || to_char(now(), 'YYYYMM') || '
 WHERE lot = 1ドル.lot
 AND machine = 1ドル.machine
 GROUP BY machine, model, lot
 ON CONFLICT ON CONSTRAINT tb_summary_unique
 DO UPDATE
 SET machine = 1ドル.machine 
 , totalshots = t.totalshots + 1
 , datetimestamp = 1ドル.datetimestamp'
 USING NEW; 
 RETURN NEW; 
END
$func$ 
LANGUAGE plpgsql;

Trigger:

DO$$
BEGIN
EXECUTE $x$CREATE TRIGGER insert_data_trigger 
 AFTER INSERT ON modelsample$x$ || to_char(now(), 'YYYYMM') || 
 ' FOR EACH ROW EXECUTE PROCEDURE 
 insert_data_func' || 
 (''modelsample''' || ',' || to_char(now(), 'YYYYMM') || ')';
END
$$;

The trigger passes the model and process date parameters to the trigger function insert_data_func(). But I get this eror msg.:

ERROR: relation "model_cd201707" does not exist
LINE 9: FROM model_cd201707

How to properly use the "$x$" on the insert_data_func() function because I think that's the reason why the model name does not get the value from model_cdvariable.

Answer 1

The error msg says relation "model_cd201707" does not exist. I suppose there is no table of that name in the search_path then? There is a confusion of table names in your question. modelsample..., model_cd..., model... tb_moldsummary. Something is not right here ...

That aside, after assigning passed parameter values to the variables model_cd and processdate you are not using them at all (yet).

Assuming your 2 passed parameters are supposed to be used to build the table name, it might work like this:

CREATE OR REPLACE FUNCTION trg_insert_data_func() 
 RETURNS TRIGGER AS 
$func$ 
BEGIN 
 EXECUTE format(
 $x$INSERT INTO tb_moldsummary AS t
 SELECT 1ドル.machine 
 , 1ドル.model
 , split_part(1ドル.lot, '_', 1)
 , right(1ドル.lot, position('_' IN lot) * -1) 
 , COUNT(*) -- never null in this query
 , 1ドル.datetimestamp
 FROM %I
 WHERE lot = 1ドル.lot
 AND machine = 1ドル.machine
 GROUP BY machine, model, lot
 ON CONFLICT ON CONSTRAINT tb_summary_unique -- constraint has same name?
 DO UPDATE
 SET machine = 1ドル.machine 
 , totalshots = t.totalshots + 1
 , datetimestamp = 1ドル.datetimestamp
 $x,ドル TG_ARGV[0]
 )
 USING NEW; 
 RETURN NEW; 
END
$func$ 
LANGUAGE plpgsql;

Trigger (passing a single concatenated parameter as table name):

DO
$$
BEGIN
 EXECUTE format(
 'CREATE TRIGGER insert_data_trigger 
 AFTER INSERT ON %1$s
 FOR EACH ROW EXECUTE PROCEDURE trg_insert_data_func(%1$L)'
 , 'modelsample' || to_char(now(), 'YYYYMM')
 );
END
$$;

Note how I use %1$s for the (unquoted) table name in the SQL statement and %1$L for the parameter (quoted as string literal). %I is not needed in this case for a known legal identifier. See the manual on format() for details.

You may want to schema-qualify function and table names to be unambiguous. Like public.modelsample. More:

• Special syntax sth.name() in the CREATE FUNCTION statement, what does it mean?

Be aware that the date component built into the trigger definition is immutable once created. Only the creating statement itself (and the trigger function) are "dynamic".

Related:

• Trigger function taking column names as parameters to modify the row

But that seems like needless complication. No need to pass the name of the table for which the trigger is called. Some special variables are available automatically in plpgsql trigger functions. Among others: TG_TABLE_SCHEMA and TG_TABLE_NAME. So use dynamic SQL in the trigger function, but the trigger itself can be simple and static:

CREATE OR REPLACE FUNCTION trg_insert_data_func() 
 RETURNS TRIGGER AS 
$func$ 
BEGIN 
 EXECUTE format(
 $x$INSERT INTO tb_moldsummary AS t
 SELECT 1ドル.machine 
 , 1ドル.model
 , split_part(1ドル.lot, '_', 1)
 , right(1ドル.lot, position('_' IN lot) * -1) 
 , COUNT(*) -- never null in this query
 , 1ドル.datetimestamp
 FROM %I.%I -- !!
 WHERE lot = 1ドル.lot
 AND machine = 1ドル.machine
 GROUP BY machine, model, lot
 ON CONFLICT ON CONSTRAINT tb_summary_unique -- constraint has same name?
 DO UPDATE
 SET machine = 1ドル.machine 
 , totalshots = t.totalshots + 1
 , datetimestamp = 1ドル.datetimestamp
 $x,ドル TG_TABLE_SCHEMA, TG_TABLE_NAME -- !!
 )
 USING NEW; 
 RETURN NEW; 
END
$func$ 
LANGUAGE plpgsql;

And no parameters in the trigger itself.

• tacticz03: I passed the parameter containing tablename on trigger function, but your answer is much better. No need to pass the paramater, much better to use the TG_TABLE_SCHEMA, TG_TABLE_NAME. Thanks Erwin. Its such a big help.