Join the Stack Overflow Community
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up

I am trying to write a trigger function in postgress 9.5. But i get an error when trying to execute the script.

My trigger:

CREATE TABLE batch (
  id bigserial PRIMARY KEY,
  batch_type varchar(60) NOT NULL,
  external_id varchar(255),
  status varchar(20) NOT NULL,
  CONSTRAINT unique_external_id UNIQUE(batch_type,external_id)
);

      CREATE TABLE batch_wip (
      batch_id BIGINT NOT NULL,
      PRIMARY KEY (batch_id)
    );

    CREATE OR REPLACE FUNCTION insert_records_into_batch_wip()
      RETURNS trigger AS
    $BODY$
    BEGIN
      IF NEW.status = 'STARTED' THEN
        INSERT INTO batch_wip(batch_id) VALUES(NEW.id);
      END IF;
      RETURN NEW;
    END;
    $BODY$
    LANGUAGE plpgsql;

    CREATE TRIGGER new_batch_record_inserts
      AFTER INSERT
      ON batch
      FOR EACH ROW
      EXECUTE PROCEDURE insert_records_into_batch_wip();

Error Iam getting,

Error occurred while executing flywayMigrate

Migration V14__add_work_in_progress_table_and_trigger.sql failed

SQL State : 42001 Error Code : 42001 Message : Syntax error in SQL statement "CREATE OR REPLACE FUNCTION[*] INSERT_RECORDS_INTO_BATCH_WIP() RETURNS TRIGGER AS $BODY$ BEGIN

IF NEW.STATUS = 'STARTED' THEN
  INSERT INTO BATCH_WIP(BATCH_ID) VALUES(NEW.ID) "; expected "FORCE, VIEW, ALIAS, SEQUENCE, USER, TRIGGER, ROLE, SCHEMA, CONSTANT, DOMAIN, TYPE, DATATYPE, AGGREGATE, LINKED, MEMORY, CACHED, LOCAL, GLOBAL, TEMP, TEMPORARY, TABLE, PRIMARY, UNIQUE, HASH, SPATIAL, INDEX"; SQL statement:

CREATE OR REPLACE FUNCTION insert_records_into_batch_wip() RETURNS trigger AS $BODY$ BEGIN IF NEW.status = 'STARTED' THEN INSERT INTO batch_wip(batch_id) VALUES(NEW.id) [42001-191] Location : /Users/bla/migration/V14__add_work_in_progress_table_and_trigger.sql (/Users/bla/migration/V14__add_work_in_progress_table_and_trigger.sql) Line : 11 Statement : CREATE OR REPLACE FUNCTION insert_records_into_batch_wip() RETURNS trigger AS $BODY$ BEGIN

IF NEW.status = 'STARTED' THEN
  INSERT INTO batch_wip(batch_id) VALUES(NEW.id)

Can someone figure out what wrong with my sql script?

share|improve this question
    
In the CREATE TRIGGER, it says ON batch, but there is no such table. Did you paste the example here correctly? – redneb Sep 6 at 22:42
    
@redneb, the batch table exists in the database. Anyway, i ll update the question to include batch table ddl – bluelabel Sep 6 at 22:46
    
I've tried it in my machine (also postgress 9.5) and it works. Also, the code looks ok. It's probably some misconfiguration issue. – redneb Sep 6 at 22:53
    
It looks like the semicolon on the INSERT line is being treated as the end of the statement. I guess the tool you're using to run it is not recognising the $BODY$ delimiters. – Nick Barnes Sep 7 at 1:07
    
Yeah, It was other configuration issue. Nothing wrong with the script. Thanks all. – bluelabel Sep 7 at 5:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.