postgresql ERROR: syntax error at or near "RETURNS"

Question

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

user330315 · Accepted Answer · 2016-02-23 07:07:14Z

3

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:

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.

answered Feb 23, 2016 at 7:07

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql ERROR: syntax error at or near "RETURNS"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related