-- 函数 - 设置表的最后更新时间
CREATE OR REPLACE FUNCTION "public"."update_timestamp_column"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
BEGIN
NEW.update_time := current_timestamp;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- test_info - 测试信息表
DROP TABLE IF EXISTS "public"."test_info";
CREATE TABLE "public"."test_info" (
  "id" int8 NOT NULL,
  "name" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
  "user_id" int8 NOT NULL DEFAULT 0,
  "phone" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
  "balance" numeric(18,4) NOT NULL DEFAULT 0,
  "sex" int2 NOT NULL DEFAULT 0,
  "birthday" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
  "sort" int4 NOT NULL DEFAULT 0,
  "status" int4 NOT NULL DEFAULT 0,
  "comment" varchar(500) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
  "create_time" int8 NOT NULL DEFAULT 0,
  "update_time" timestamp(3) NOT NULL DEFAULT pg_systimestamp(),
  "is_delete" int2 NOT NULL DEFAULT 0
)
;
-- 设置主键
ALTER TABLE "public"."test_info" ADD CONSTRAINT "PK_test_info_pkey" PRIMARY KEY ("id");
-- 索引
CREATE INDEX "IDX_test_info_create_time" ON "public"."test_info"("create_time");
-- 触发器：自动赋值最后更新时间
CREATE TRIGGER "TG_test_info" BEFORE INSERT OR UPDATE OF "update_time" ON "public"."test_info"
FOR EACH ROW
EXECUTE PROCEDURE "public"."update_timestamp_column"();
-- 添加描述
COMMENT ON TABLE "public"."test_info" IS '测试信息表';
COMMENT ON COLUMN "public"."test_info"."id" IS '主键';
COMMENT ON COLUMN "public"."test_info"."name" IS '名称';
COMMENT ON COLUMN "public"."test_info"."user_id" IS '用户id';
COMMENT ON COLUMN "public"."test_info"."phone" IS '电话';
COMMENT ON COLUMN "public"."test_info"."balance" IS 'balance余额';
COMMENT ON COLUMN "public"."test_info"."sex" IS '性别 男=1,女=2,其他=3';
COMMENT ON COLUMN "public"."test_info"."birthday" IS '生日';
COMMENT ON COLUMN "public"."test_info"."sort" IS '排序值';
COMMENT ON COLUMN "public"."test_info"."status" IS '状态 EState';
COMMENT ON COLUMN "public"."test_info"."comment" IS '备注';
COMMENT ON COLUMN "public"."test_info"."create_time" IS '数据创建时间';
COMMENT ON COLUMN "public"."test_info"."update_time" IS '最后更新时间';
COMMENT ON COLUMN "public"."test_info"."is_delete" IS '是否删除(逻辑删除)';