GitLab

Created by: alesso-x

MIgration

ALTER TABLE public.usage_type
  ADD COLUMN is_weather_related BOOLEAN DEFAULT TRUE;

INSERT INTO public.usage_type (description, is_weather_related) VALUES
  ('dhw', FALSE),
  ('lighting', FALSE),
  ('cooking', FALSE),
  ('plug_load', FALSE);

CREATE TABLE non_weather_related_usage (
  id            SERIAL PRIMARY KEY,
  account_id    INTEGER REFERENCES account ON DELETE CASCADE,
  usage_type_id INTEGER REFERENCES usage_type,
  value         NUMERIC(3, 0)
);

GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE public.non_weather_related_usage TO "building_service"

ALTER TABLE public.disaggregated_bill
  ADD COLUMN dhw FLOAT,
  ADD COLUMN lighting FLOAT,
  ADD COLUMN cooking FLOAT,
  ADD COLUMN plug_load FLOAT,
  ADD COLUMN miscellaneous FLOAT;

DROP FUNCTION IF EXISTS public.delete_disaggregated_bill( INTEGER );

CREATE OR REPLACE FUNCTION public.delete_disaggregated_bill(IN in_account_id INTEGER)
  RETURNS TABLE(
    id             INTEGER,
    account_id     INTEGER,
    bill_from_date DATE,
    bill_to_date   DATE,
    heating_usage  FLOAT,
    cooling_usage  FLOAT,
    other_usage    FLOAT,
    dhw            FLOAT,
    lighting       FLOAT,
    cooking        FLOAT,
    plug_load      FLOAT,
    miscellaneous  FLOAT
  ) AS $BODY$

BEGIN
  RETURN QUERY
  DELETE FROM disaggregated_bill
  WHERE public.disaggregated_bill.account_id = in_account_id
  RETURNING *;
END;

$BODY$
LANGUAGE plpgsql
VOLATILE
COST 100
ROWS 1000;