Postgres function with nested IF & ELSE statement error-CodePudding

I have this function where it looks up all the users and if any one does not exists do not create the function. What is wrong?

  RETURNS event_trigger AS $$
DECLARE
  audit_query TEXT;
  r RECORD;
BEGIN
  IF tg_tag IN ('CREATE TABLE', 'CREATE TABLE AS') 
  THEN  
  IF EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'test' or rolname = 'testa')
  THEN
    FOR r IN 
    SELECT * FROM pg_event_trigger_ddl_commands() AS ddl WHERE ddl.schema_name IN ('testb','testc','testd') 
    LOOP
      INSERT INTO user_monitor.ddl_history (ddl_date, ddl_tag, object_name) VALUES (statement_timestamp(), tg_tag, r.object_identity);
    END LOOP;
   else
    RAISE EXCEPTION 'Not all users found'; 
  END IF;
END;
$$ LANGUAGE plpgsql

SQL Error [42601]: ERROR: syntax error at or near ";"
SQL Error [42601]: ERROR: missing "THEN" at end of SQL expression
  Position: 286

CodePudding user response：

An excerpt from the manual:

Event triggers are created using the command CREATE EVENT TRIGGER. In order to create an event trigger, you must first create a function with the special return type event_trigger.

This function need not (and may not) return a value; the return type serves merely as a signal that the function is to be invoked as an event trigger.

Emphasis on the second sentence.

You should probably replace your RETURN with RAISE EXCEPTION instead.
https://www.postgresql.org/docs/current/event-trigger-table-rewrite-example.html