metastore/scripts/upgrade/postgres/017-HIVE-6458.postgres.sql

SELECT '< HIVE-6458 Add schema upgrade scripts for metastore changes related to permanent functions >';

CREATE TABLE "FUNCS" (
  "FUNC_ID" BIGINT NOT NULL,
  "CLASS_NAME" VARCHAR(4000),
  "CREATE_TIME" INTEGER NOT NULL,
  "DB_ID" BIGINT,
  "FUNC_NAME" VARCHAR(128),
  "FUNC_TYPE" INTEGER NOT NULL,
  "OWNER_NAME" VARCHAR(128),
  "OWNER_TYPE" VARCHAR(10),
  PRIMARY KEY ("FUNC_ID")
);

CREATE UNIQUE INDEX "UNIQUEFUNCTION" ON "FUNCS" ("FUNC_NAME", "DB_ID");
CREATE INDEX "FUNCS_N49" ON "FUNCS" ("DB_ID");

ALTER TABLE ONLY "FUNCS"
    ADD CONSTRAINT "FUNCS_FK1" FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID") DEFERRABLE;

CREATE TABLE "FUNC_RU" (
  "FUNC_ID" BIGINT NOT NULL,
  "RESOURCE_TYPE" INTEGER NOT NULL,
  "RESOURCE_URI" VARCHAR(4000),
  "INTEGER_IDX" INTEGER NOT NULL,
  PRIMARY KEY ("FUNC_ID", "INTEGER_IDX")
);

CREATE INDEX "FUNC_RU_N49" ON "FUNC_RU" ("FUNC_ID");

ALTER TABLE ONLY "FUNC_RU"
    ADD CONSTRAINT "FUNC_RU_FK1" FOREIGN KEY ("FUNC_ID") REFERENCES "FUNCS" ("FUNC_ID") DEFERRABLE;