ERROR: unterminated dollar-quoted string at or near "$BODY$

Question

I'm using sql fiddle...PostgreSQL 9.3:

CREATE TABLE HotelStays
(roomNum INTEGER NOT NULL,
arrDate DATE NOT NULL,
depDate DATE NOT NULL,
guestName CHAR(30) NOT NULL,
PRIMARY KEY (roomNum, arrDate))
;


CREATE OR REPLACE FUNCTION new_customer() RETURNS void AS
$BODY$
DECLARE
    depatureDate DATE;\
BEGIN
    SELECT depDate INTO depatureDate FROM HotelStays WHERE OLD.roomNum = NEW.roomNum;
    IF (depatureDate <= NEW,arrDate)
      INSERT INTO HotelStays (roomNum, arrDate, depDate, guestName)
    VALUES (:NEW.roomNum, :NEW.arrDate, :NEW.depDate, :NEW.guestName);
    END IF;
    RETURN;
END
$BODY$
LANGUAGE 'plpgsql' ;
CREATE TRIGGER;

INSERT INTO HotelStays(roomNum, arrDate, depDate, guestName)
VALUES 
(123, to_date('20160202', 'YYYYMMDD'), to_date('20160206','YYYYMMDD'), 'A');

Problem I am trying to solve: a new entry (for a new guest) could be put in for a room number, even before the existing guest has checked out. I'm trying to solve this question using triggers. Please help me out. Thanks in advance.

Answer 1

As author mentioned, he used SQL Fiddle. I had the same problem with db-fiddle.com and resolved it by replacing $$ or $BODY$ with single quotes ' (and doubling single quotes elsewhere in between.

For example, db-fiddle

CREATE OR REPLACE FUNCTION update_datem()
  RETURNS trigger AS
'
BEGIN
 NEW.dateM = DATE_TRUNC(''MONTH'', NEW.date);
 RETURN NEW;
END;
'
LANGUAGE plpgsql;

Answer 2

There are several errors in your code. First the backslash in depatureDate DATE;\. You are also missing a THEN for the IF clause and new does not need a : in front of it. You also have a , instead of a . in NEW,arrDate. And the final END is missing a ;.

Not an error, but the language name is an identifier, do not put it in single quotes.

The line CREATE TRIGGER; is also wrong. If you want to create trigger your function also needs to be declared as returns trigger and has to return the new row if it is a "before" trigger. If you intend to use an after trigger you still need to return something from that.

I am not sure what the condition WHERE OLD.roomNum = NEW.roomNum; is supposed to select. If you want to get the room number of the changed row, just use new.depdate. The select .. into ... will fail if that query returns more then one row. You probably meant to use where roomnum = new.roomnum or something similar.

So the function should be something like this:

CREATE OR REPLACE FUNCTION new_customer() 
  RETURNS trigger 
AS
$BODY$
DECLARE
    depatureDate DATE;
BEGIN
    SELECT depDate 
       INTO depatureDate 
    FROM HotelStays 
    WHERE roomNum = NEW.roomNum;

    IF (depatureDate <= NEW.arrDate) THEN
      INSERT INTO HotelStays (roomNum, arrDate, depDate, guestName)
      VALUES (NEW.roomNum, nEW.arrDate, NEW.depDate, NEW.guestName);
    END IF;

    RETURN NEW; -- this is important for a trigger
END;
$BODY$
LANGUAGE plpgsql;

And the code to create the trigger would be something like this:

CREATE TRIGGER check_stays
  before update or insert on hotelstays
  execute procedure new_customer();