CODEWITHSUNDEEP
postgresqltriggers
Stijn Berendse
Stijn Berendse

Reputation: 75

Creating a trigger gives "function does not exist" error in PostgreSQL

I'm working on a project right now where I'm trying to build a trigger that updates a column "lastedit" (in the table Person), which is a timestamp storing when the last change was made to the table in the schema specified in the trigger creation (in this case Certifications).

Now, my problem is that when I try to create the trigger, after creating the function "update_lastedit()", it gives me an error saying that the function does not exist. I think I might have a mismatch in my function somewhere, but I cannot seem to find it.

Could any of you help me out? I'm running PostgreSQL 9.5.5. Please let me know if I need to give a more extensive explanation, this is my first question, so I might have overlooked something important.

My code for the trigger and the function are as follows:

CREATE OR REPLACE FUNCTION update_lastedit() RETURNS TRIGGER AS
$update_edit$
BEGIN
    UPDATE ovsoftware.person
    SET lastedit = now();
END;
$update_edit$
LANGUAGE plpgsql;

and

CREATE TRIGGER cert_edit_trigger
    BEFORE INSERT OR UPDATE ON ovsoftware.certifications
    FOR EACH ROW
    EXECUTE PROCEDURE update_lastedit();

The exact error:

SQL fout:

ERROR:  function update_lastedit() does not exist
In statement:

CREATE TRIGGER cert_edit_trigger
    BEFORE INSERT OR UPDATE ON ovsoftware.certifications
    FOR EACH ROW
    EXECUTE PROCEDURE update_lastedit();

Upvotes: 2

Views: 5309

Answers (1)

Stijn Berendse
Stijn Berendse

Reputation: 75

The solution was to use a fully qualified name for the function as follows: ovsoftware.update_lastedit(). I am not sure why that is the case, as I did not need to do so in other cases. Either way, the scope apparently did not include the ovsoftware schema, leading to the error.

Upvotes: 1

Related Questions