3

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?

  • and the problem is? – ypercubeᵀᴹ Jul 7 '17 at 8:24
  • @ypercubeᵀᴹ please check, I updated my question. – tacticz03 Jul 7 '17 at 8:28
  • Did you try to use USING as the answers in the linked question? – ypercubeᵀᴹ Jul 7 '17 at 8:34
  • 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 '17 at 8:49
  • 1
    That's not what the 2 answers suggest. You shoouldn't use NEW inside the string. – ypercubeᵀᴹ Jul 7 '17 at 10:06
2

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)
    
  • [Err] ERROR: column reference "totalshots" is ambiguous LINE 16: , totalshots = totalshots + 1 I got this error that's why I identify the table totalshots = tb_moldsummary.totalshots + 1. By the way, your code works. Thanks Erwin. – tacticz03 Jul 10 '17 at 3:18
  • @tacticz03: Funny, I was just fixing this error which caught my attention at a second look. – Erwin Brandstetter Jul 10 '17 at 3:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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