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.