CODEWITHSUNDEEP
postgresql
Sunilkumar Vuppala
Sunilkumar Vuppala

Reputation: 171

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

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.

Upvotes: 17

Views: 32462

Answers (5)

Graham Laight
Graham Laight

Reputation: 4840

Search for $ symbols in the query. I was caught out by $ symbols in commented out code: apparently, it still counts!

Upvotes: 0

李华南
李华南

Reputation: 1

I just encountered a similar problem and found that there is a missing $symbol at the end. I hope it can help you

Upvotes: 0

Abel Callejo
Abel Callejo

Reputation: 14939

In my case I learned the hard way that it was not about my code syntax. It turns out that the SQL client software I was using was the one causing the error. It was not able to handle the dollar sign properly.

The way I figured it out was by putting my plpgsql code inside a file and executed it via the command-line like so:

Step 1

my_function.plpgsql

CREATE OR REPLACE FUNCTION my_function() RETURNS VOID AS $$
BEGIN
    -- Your PL/pgSQL code here
    RAISE NOTICE 'This is a test function.';
END;
$$ LANGUAGE plpgsql;

Step 2

psql -h hostname -U username -d databasename -f "/path/to/my_function.plpgsql"

Details of my case

Maybe this is just in my case 🤦 that it did not work with HeidiSQL. There are probably other causes for such error to happen though. However, if you had the same scenario as mine, please comment down the SQL client you were using so that we can note it.

Upvotes: 2

Sergey Nudnov
Sergey Nudnov

Reputation: 1429

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;

Upvotes: 36

user330315
user330315

Reputation:

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();

Upvotes: 2

Related Questions