4

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?

1
  • 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(NEW.lot), NEW.datetimestamp FROM (SELECT $1.*) AS t' USING NEW; I tried this one but the same error occurs
    – tacticz03
    Jul 7, 2017 at 8:49

1 Answer 1

4

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):

  • 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)
    
4

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.