创建触发器功能是psql“语法错误在或接近”CREATE“”(Creating Trigger function is psql “syntax error at or near ”CREATE“”)

这是我的postgreSQL代码

CREATE FUNCTION connectedExhibitionFunction() RETURNS trigger AS $$ BEGIN IF (SELECT COUNT("exName") FROM Exhibitions WHERE NEW."exName" = "exName") > 0 THEN IF (SELECT Count(doorLoc1) FROM Doors, ExhibitionLocations WHERE (dorLoc1=NEW.elLocation AND dorLoc2=elLocations) OR (dorLoc2=NEW.elLocation AND dorLoc1=elLocations) > 0 THEN RAISE EXCEPTION 'You can't have the exhibition there, the same exhibition is in an unconnected room'; END IF; END IF; END; $$ LANGUAGE plpgsql; CREATE TRIGGER connectedExhibitionTrigger BEFORE INSERT ON ExhibitionsLocations EXECUTE PROCEDURE connectedExhibitionFunction();

这就是我得到的错误

psql:file.txt:62: ERROR: syntax error at or near "CREATE" LINE 8: CREATE FUNCTION connectedExhibitionFunction() ^ psql:file.txt:67: ERROR: current transaction is aborted, commands ignored until end of transaction block

我似乎无法弄清楚错误，任何人都可以在这里找到任何东西吗？

Here is my postgreSQL code

CREATE FUNCTION connectedExhibitionFunction() RETURNS trigger AS $$ BEGIN IF (SELECT COUNT("exName") FROM Exhibitions WHERE NEW."exName" = "exName") > 0 THEN IF (SELECT Count(doorLoc1) FROM Doors, ExhibitionLocations WHERE (dorLoc1=NEW.elLocation AND dorLoc2=elLocations) OR (dorLoc2=NEW.elLocation AND dorLoc1=elLocations) > 0 THEN RAISE EXCEPTION 'You can't have the exhibition there, the same exhibition is in an unconnected room'; END IF; END IF; END; $$ LANGUAGE plpgsql; CREATE TRIGGER connectedExhibitionTrigger BEFORE INSERT ON ExhibitionsLocations EXECUTE PROCEDURE connectedExhibitionFunction();

And this is the error I'm getting

psql:file.txt:62: ERROR: syntax error at or near "CREATE" LINE 8: CREATE FUNCTION connectedExhibitionFunction() ^ psql:file.txt:67: ERROR: current transaction is aborted, commands ignored until end of transaction block

I can't seem to figure out the error, can anybody spot anything here?

最满意答案

我猜你错过了选择“）”并且提出异常条款不能“不能”而你只能使用END而不是END IF。据我所知，你的问题就是这些。

请试试这个。

IF (SELECT Count(doorLoc1) FROM Doors, ExhibitionLocations WHERE (dorLoc1=NEW.elLocation AND dorLoc2=elLocations) OR (dorLoc2=NEW.elLocation AND dorLoc1=elLocations)) > 0 THEN RAISE EXCEPTION 'You cant have the exhibition there, the same exhibition is in an unconnected room'; END

I guess you missed Select ")" and raise exception clause couldnt be "can't" And you can use just END instead of END IF. As far as I know your problem is these.

Try this please.

IF (SELECT Count(doorLoc1) FROM Doors, ExhibitionLocations WHERE (dorLoc1=NEW.elLocation AND dorLoc2=elLocations) OR (dorLoc2=NEW.elLocation AND dorLoc1=elLocations)) > 0 THEN RAISE EXCEPTION 'You cant have the exhibition there, the same exhibition is in an unconnected room'; END

更多推荐