syntax error at or near "CREATE" when making a trigger in postgresql

Question

i'm trying to make a trigger that checks when a row is entered into a table if a certain value in that row exists in another table. the tables i have are:

car, rent, request

the code i'm using:

CREATE OR REPLACE FUNCTION trigf1() RETURNS trigger AS $$
BEGIN
SELECT seats FROM car WHERE lno=new.lno;

IF seats=!(SELECT seats FROM request WHERE rno=new.rno)
THEN
raise exception 'assignment contradicts design'
END IF;
RETURN null;
END;
$$

CREATE TRIGGER 'T1' BEFORE INSERT ON rent
for each row
EXECUTE PROCEDURE trigf1( );

I get an error saying:

ERROR: syntax error at or near "CREATE"

LINE 13: CREATE TRIGGER 'T1' BEFORE INSERT IN rent

anyone knows why?

Identifiers are written without single quotes: create trigger t1 ... — a_horse_with_no_name
This $$ needs to be $$;. Without it you did not finish the function code. — Adrian Klaver

matan ben simon matan ben simon · Accepted Answer · 2020-11-08T23:07:35

well, don't know why but this solved it:

CREATE OR REPLACE FUNCTION trigf1() RETURNS trigger AS $$
BEGIN
SELECT seats FROM car WHERE lno=new.lno;

IF seats=!(SELECT seats FROM request WHERE rno=new.rno)
THEN
raise exception 'assignment contradicts design';
END IF;
RETURN null;
END;
$$language plpgsql;

CREATE TRIGGER T1 BEFORE INSERT ON rent
for each row
EXECUTE PROCEDURE trigf1( );

I could use an explanation if anyone knows why :)

syntax error at or near "CREATE" when making a trigger in postgresql

1 Answers