CODEWITHSUNDEEP
postgresqlplpgsqldatabase-trigger
fatyuan
fatyuan

Reputation: 23

postgresql ERROR: syntax error at or near "RETURNS"

I'm completely new to psql. And when I use http://sqlfiddle.com/ to do some homework, the system return this error. 

ERROR: syntax error at or near "RETURNS"

Any help is appreciated. Here is my psql:

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 stopInsert RETURNS trigger AS
$body$
DECLARE
  availableArrDate DATE;
  checkRoomNum INTEGER;
BEGIN
  if (NEW.arrDate >= NEW.depDate) then
    return null;
   end if;

  checkRoomNum = NEW.roomNum;

  select h.depDate into availableArrDate
  from HotelStays h
  where h.roomNum = checkRoomNum
  order by h.depDate DESC
  LIMIT 1;
  if (availableArrDate > NEW.arrDate)
    return null;
  end if;
END;
$body$ LANGUAGE plpgsql;

create trigger stopInsert before insert ON HotelStays 
For each row
execute procedure stopInsert();

Upvotes: 2

Views: 3244

Answers (1)

user330315
user330315

Reputation:

The function name needs to have () in it: 

CREATE OR REPLACE FUNCTION stopInsert() RETURNS trigger AS
                                      ^
--------------------------------------|

The IF statement is incorrect as well, you are missing the THEN

if (availableArrDate > NEW.arrDate) then --<< THEN is required
  return null;
end if;

In SQLFiddle you also need to use a different statement terminator to be able to use the embedded ; in the PL/pgSQL code:

enter image description here

You then keep the ; inside the function, but replace all "final" ; with the / on a single line. This is only required for SQLFiddle, not when you use e.g. the command line client psql or other Postgres compatible SQL clients.

Upvotes: 3

Related Questions