Pass Parameters to trigger function to execute dynamic SQL

dynamic-sqlfunctionsinsertplpgsqltrigger

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.

Best Answer

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:

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:

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.

Related Question