Execute dynamic INSERT ON CONFLICT DO UPDATE in plpgsql function

Question

I have this table that is created every month. ex. model201707 ('model' = fixed; '201707' = year & month based on the current date):

| COLUMN_NAME   | TYPE        |
|---------------|-------------|
| serial        | varchar     |
| lot           | varchar     |
| model         | varchar     |
| line          | varchar     |
| machine       | varchar     |
| datetimestamp | timestamptz |

Example values:

| serial | lot        | model  | line | machine | datetimestamp       |
|--------|------------|--------|------|---------|---------------------|
| 1      | 16_F22_F23 | sample | A    | 1       | 2017-02-28 07:22:39 |
| 2      | 16_F22_F23 | sample | A    | 1       | 2017-02-28 07:22:42 |
| 3      | 16_F22_F23 | sample | A    | 1       | 2017-02-28 07:22:45 |

I have created a function to get data from table model201707 and insert it into table tb_moldsummary:

| COLUMN_NAME   | TYPE      |    |    |     |
|---------------|-----------|----|----|-----|
| machine_name  | varchar   | NN | PK |     |
| model         | varchar   | NN | PK | UNQ |
| mold          | varchar   | NN | PK | UNQ |
| cavity        | varchar   | NN | PK | UNQ |
| totalshots    | int4      |    |    |     |
| datetimestamp | timestamp |    |    |     |

Example values:

| machine | model  | m_id | cav_id  | totalshots | datetimestamp       |
|---------|--------|------|---------|------------|---------------------|
| 1       | sample | 16   | F22_F23 | 3          | 2017-02-28 07:22:45 |

Function:

CREATE OR REPLACE FUNCTION insert_data_func() 
RETURNS TRIGGER AS 
$BODY$ 

EXECUTE 'INSERT INTO tb_moldsummary '
    ||  'SELECT NEW.machine, NEW.model, split_part(NEW.lot, ''_'', 1), '
    ||  'SUBSTRING(NEW.lot FROM (POSITION(''_'' in NEW.lot)+1) FOR LENGTH(NEW.lot)), COUNT(lot), NEW.datetimestamp '
    ||  'FROM model' || to_char(CURRENT_TIMESTAMP, 'YYYYMM') 
    ||  ' WHERE lot = NEW.lot AND machine = NEW.machine GROUP BY machine, model, lot '
    ||  'ON CONFLICT ON CONSTRAINT tb_summary_unique '
    ||  'DO UPDATE SET '
    ||  'machine = NEW.machine, '
    ||  'totalshots = tb_moldsummary.totalshots + 1, '
    ||  'datetimestamp = NEW.datetimestamp ';


RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER insert_data_trigger 
AFTER INSERT ON model201707
FOR EACH ROW 
EXECUTE PROCEDURE insert_data_func();

Error:

[Err] ERROR:  missing FROM-clause entry for table "new"
LINE 1: INSERT INTO tb_moldsummary SELECT NEW.machine, NEW.model, sp...

I already checked the following links, but I'm confused:

PLPGSQL STATEMENTS EXECUTING DYNAMIC SQL

insert-record-into-a-table-in-a-trigger-function

What's the correct syntax for this dynamic UPSERT?

Answer 1

Multiple problems. This should work:

CREATE OR REPLACE FUNCTION insert_data_func() 
  RETURNS TRIGGER AS 
$func$ 
BEGIN  -- !
   EXECUTE
      $x$INSERT INTO tb_moldsummary AS t
         SELECT $1.machine                               -- !
              , $1.model
              , split_part(lot, '_', 1)
              , right(lot, position('_' IN lot) * -1)    -- ! simpler
              , 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; -- can be RETURN NULL for AFTER trigger
END
$func$  LANGUAGE plpgsql;

I marked lines with important fixes.

Major points

• Pass NEW to EXECUTE with the USING clause (as instructed in the answers to the question you referenced):

  • Insert RECORD into a table in a trigger function?

• Dollar-quoting helps to simplify the syntax for string literals containing single quotes.

• Replace

  SUBSTRING(NEW.lot FROM (POSITION('_' in NEW.lot)+1) FOR LENGTH(NEW.lot))
  

  with this simpler and faster equivalent expression:

  right(NEW.lot, position('_' IN NEW.lot) * -1)