Very often we are humbled by the simplest things and tonight I got a good one.
I had created a plpgsql function that was called from within a trigger to check for some duplicates in a table, blah blah. The function was working because I had tested it. It was registered in the “pg_proc” table and the two “proargtypes” were type 1043 ie varchar. This function was there, I could see it and if I was a character in Tron I could touch it, so why the hell when one of my scripts ran did I get: function foo does not exist.
I’ll tell you why, I was not using the correct schema. Ahhhhhhhhhhhh.
I had tested it while logged in as the user who created it and that users schema is different from the user that needs to use it from the website. A quick
grant usage on schema foo to bar;
sorted that problem.