20160712191217-create-useractivestatus.sql

BEGIN;
  CREATE OR REPLACE VIEW UserActiveStatus AS (
    SELECT us1.*
    FROM "UserStatus" us1
    LEFT JOIN "UserStatus" us2 ON (us1."UserId" = us2."UserId" AND us1."updatedAt" < us2."updatedAt")
    WHERE us2."id" IS NULL
  );

  CREATE OR REPLACE FUNCTION UserActiveStatus_trg_ioi_fn()
  RETURNS TRIGGER
  LANGUAGE plpgsql
  AS $$
  BEGIN
    IF TG_OP = 'INSERT' THEN
      INSERT INTO "UserStatus"
      VALUES(NEW."id", NEW."UserId", NEW."AdministratorId", NEW."status", NEW."detail", NEW."createdAt", NEW."updatedAt");
      RETURN NEW;
    ELSE
      RAISE EXCEPTION 'This view only supports INSERT statements' USING ERRCODE = '0A000';
    END IF;
  RETURN NEW;
  END;
  $$;

  DROP TRIGGER IF EXISTS UserActiveStatus_trg_ioi ON UserActiveStatus;

  CREATE TRIGGER UserActiveStatus_trg_ioi
  INSTEAD OF INSERT OR UPDATE OR DELETE ON UserActiveStatus
  FOR EACH ROW EXECUTE PROCEDURE UserActiveStatus_trg_ioi_fn();
END;