2

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();
9
  • please update post with exact error - your provided code sample is ok Commented Jun 8, 2017 at 7:47
  • Done, thanks for the feedback! Commented Jun 8, 2017 at 7:51
  • 3
    Are you sure that they are in the same database and in the same schema? (If they are supposed to be in another schema, but in the same database, you can use fully qualified names everywhere). Commented Jun 8, 2017 at 8:05
  • 3
    Sidenote: I'm hoping that the trigger body is just some example code. If that would be a real trigger body, every insert or update on ovsoftware.certifications would rewrite the whole ovsoftware.person table & leave a ton of dead tuples in it (because it updates every row in it). Commented Jun 8, 2017 at 8:06
  • 1
    You want: new.lastedit := now(); in the trigger, not an UPDATE statement. Commented Jun 8, 2017 at 9:32

1 Answer 1

1

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.