I have the following trigger function that uses the moddatetime extension:
/* BEFORE UPDATE trigger - Updates "entity_version.updated_on", setting it to the current UTC time. */
CREATE EXTENSION IF NOT EXISTS moddatetime; -- Needs superuser privileges!
DROP TRIGGER IF EXISTS trig_entity_version_before_update
ON entity_version;
CREATE TRIGGER trig_entity_version_before_update
BEFORE UPDATE
ON entity_version
FOR EACH ROW EXECUTE PROCEDURE moddatetime(updated_on); -- updated_on is TIMESTAMP type
The trigger works perfectly fine, but the issue is that the first line (CREATE EXTENSION) requires super-user privileges. Since these databases are going to be created by users (via a script) I don't want them the user that makes these databases and triggers to have super user access.
As a first step, running the script as a super user works fine, as you'd expect.
Naturally, the next step would be to separate the creation of the extension as a superuser from the trigger creation script. But doing that, if I run the above script without CREATE EXTENSION line, I get the following error:
function moddatetime() does not exist
Which I suppose makes sense given that the script never declares moddatetime, but I cannot find anywhere in the documentation how to define the extension as available without using CREATE EXTENSION. Surely there must be a way to import or use an extension without having the create it? Something akin to:
USING EXTENSION moddatetime; -- Does something like this exist?
... rest of the trigger script ...
You need the extension installed if you want to use it.
PostgreSQL v13 introduces the notion of a trusted extension, which can be installed by non-superusers with the CREATE privilege on the database, but this extension is not among them. So upgrading won't fix the problem.
You could connect to template1 as superuser and create the extension there. Then it would automatically be present in all newly created databases.
But frankly, I don't see the point. You can do the same thing with a regular PL/pgSQL trigger – only the performance wouldn't be as good.