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
|
)
|
;
|
COMMENT ON TABLE "public"."test_info" IS '测试信息表';
|
COMMENT ON COLUMN "public"."test_info"."id" IS '主键id';
|
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 '状态 正常=200,停用=300,删除=400';
|
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 '是否删除(逻辑删除)';
|
-- 设置主键
|
ALTER TABLE "public"."test_info" ADD CONSTRAINT "PK_test_info" 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"();
|
