Function does not exist in PostgreSQL

Question

I have this Postgres code

CREATE PROCEDURE adjust_available(patientBed UUID)
LANGUAGE SQL
AS $$
    UPDATE bed
    SET available = false
    WHERE bed.id = patientBed;
$$;

CREATE TRIGGER adjust_available
    AFTER UPDATE OF bed ON patient
    FOR EACH ROW
    EXECUTE PROCEDURE adjust_available(bed);

However when you run this query. It says the function does not exist. However, you create it above?

The full error:

ERROR: function adjust_available() does not exist SQL state: 42883

Answer 1

Though the CREATE TRIGGER statement allows a PROCEDURE keyword for the EXECUTE clause, it doesn't actually allow procedures to be executed. From the documentation:

In the syntax of CREATE TRIGGER, the keywords FUNCTION and PROCEDURE are equivalent, but the referenced function must in any case be a function, not a procedure. The use of the keyword PROCEDURE here is historical and deprecated.

Instead, you must use a trigger function, which must not declare arguments (though arguments can be accessed as strings via the special TG_ARGV[] array variable) and must have a return type of trigger or, for event triggers, event_trigger. Speaking generally & simplistically, functions for row-level BEFORE and INSTEAD OF triggers should return NULL to cancel the rest of the operation and NEW to continue. The return value is ignored by AFTER and statement-level BEFORE triggers; it may as well be NULL.

Based on the question sample code (untested):

CREATE FUNCTION adjust_available()
  RETURNS trigger
  LANGUAGE plpgsql
  AS $$
  BEGIN
    UPDATE bed
      SET available = false
      WHERE bed.id = TG_ARGV[0]::UUID;
    RETURN NULL; -- return value for AFTER trigger is ignored
  END
  $$;

(Transferred from the question comments.)