Write plain moddatetime procedute to use in triggers

Question

My cloud provider won't allow me to install the moddatetime extension. However, my DB schema relies on the moddatetime procedure.

How could I write a plain PSQL drop-in replacement for the moddatetime(colname) function that can be used in place of this extension ? (Here is the specification of this extension)

CREATE FUNCTION moddatetime() RETURNS trigger
  -- ???

Answer 1

The following function will act as a drop-in replacement for moddatetime.

CREATE OR REPLACE FUNCTION moddatetime() RETURNS trigger LANGUAGE plpgsql AS $moddatetime$
  DECLARE
    colname name;
  BEGIN
    IF (TG_NARGS = 1) THEN
      colname = TG_ARGV[0];
    ELSE
      RAISE EXCEPTION 'moddatetime(colname) requires one argument';
    END IF;

    RETURN json_populate_record(NEW, json_build_object(colname, NOW()));
  END;
$moddatetime$;

If you want to use the native one and fallback if the native extension cannot be installed, use the following:

DO $SETUP_MODDATETIME$
BEGIN
  CREATE EXTENSION IF NOT EXISTS "moddatetime";
EXCEPTION WHEN INSUFFICIENT_PRIVILEGE THEN
  -- Unable to use moddatetime extension, so create a function instead
  CREATE OR REPLACE FUNCTION moddatetime() RETURNS trigger LANGUAGE plpgsql AS $moddatetime$
    DECLARE
      colname name;
    BEGIN
      IF (TG_NARGS = 1) THEN
        colname = TG_ARGV[0];
      ELSE
        RAISE EXCEPTION 'moddatetime(colname) requires one argument';
      END IF;

      RETURN json_populate_record(NEW, json_build_object(colname, NOW()));
    END;
  $moddatetime$;
END $SETUP_MODDATETIME$;