问题

我的云服务提供商不允许我安装`moddatetime`扩展。然而，我的数据库架构依赖于`moddatetime`过程。

我该如何编写一个纯粹的PSQL替代品，代替这个扩展中的`moddatetime(colname)`函数？（[这是该扩展的规范][1]）

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

<details>
<summary>英文:</summary>

My cloud provider won&#39;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][1])

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

答案1

得分: 0

以下函数将充当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)需要一个参数';
    END IF;

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

如果你想使用原生的，如果无法安装原生扩展，则退回到这个：

DO $SETUP_MODDATETIME$
BEGIN
  CREATE EXTENSION IF NOT EXISTS "moddatetime";
EXCEPTION WHEN INSUFFICIENT_PRIVILEGE THEN
  -- 无法使用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)需要一个参数';
      END IF;

      RETURN json_populate_record(NEW, json_build_object(colname, NOW()));
    END;
  $moddatetime$;
END $SETUP_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$;

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$;