Функции в PostgreSQL. Проектирование структуры БД жилищной управляющей компании, страница 2

      begin

      perform log_new('bill_delete');

      delete from  bill where id = $1;

      return 'Done';

      end;$BODY$

  LANGUAGE 'plpgsql' VOLATILE

CREATE OR REPLACE FUNCTION bill_edit(pid integer, pperson integer, pdate date, pjob integer)

  RETURNS character AS

$BODY$

      begin

      perform log_new('bill_edit');

      update bill set person_id = pperson, bill_date = pdate, job_id = pjob where id = pid;

      return 'Done';

      end;$BODY$

  LANGUAGE 'plpgsql' VOLATILE

CREATE OR REPLACE FUNCTION bill_new(pperson integer, pdate date, pjob integer)

  RETURNS character AS

$BODY$

      begin

      perform log_new('bill_new');

      insert into bill (person_id, bill_date, job_id) values(pperson, pdate, pjob);

      return 'Done';

      end;$BODY$

  LANGUAGE 'plpgsql' VOLATILE

CREATE OR REPLACE FUNCTION job_complete(integer, boolean)

  RETURNS character AS

$BODY$

      begin

      perform log_new('job_complete');

      update job set completed = $2

            where id = $1;

      return 'Done';

      end;$BODY$

  LANGUAGE 'plpgsql' VOLATILE

CREATE OR REPLACE FUNCTION job_delete(pid integer)

  RETURNS character AS

$BODY$

      declare vtaskid int;

            vworkerid int;

      begin

      perform log_new('job_delete');

      delete from  job where id = pid;

      return 'Done';

      end;$BODY$

  LANGUAGE 'plpgsql' VOLATILE

CREATE OR REPLACE FUNCTION job_edit(pid integer, ptask character varying, pworker character varying, pdate date, pcompleted boolean)

  RETURNS character AS

$BODY$

      declare vtaskid int;

            vworkerid int;

      begin

      perform log_new('job_edit');

      select id from workers where name = pworker into vworkerid;

      if (vworkerid is null) then return 'No such worker found';

      end if;

      select id from task where name = ptask into vtaskid;

      if (vtaskid is null) then return 'No such task found';

      end if;

      update job set task_id = vtaskid, worker_id = vworkerid, job_date = pdate, completed = pcompleted

            where id = pid;

      return 'Done';

      end;$BODY$

  LANGUAGE 'plpgsql' VOLATILE

CREATE OR REPLACE FUNCTION job_new(ptask character varying, pworker character varying, pdate date, pcompleted boolean)

  RETURNS character AS

$BODY$

      declare vtaskid int;

            vworkerid int;

      begin

      perform log_new('job_new');

      select id from workers where name = pworker into vworkerid;

      if (vworkerid is null) then return 'No such worker found';

      end if;

      select id from task where name = ptask into vtaskid;

      if (vtaskid is null) then return 'No such task found';

      end if;

      insert into job (task_id, worker_id, job_date, completed) values(vtaskid, vworkerid, pdate, pcompleted);

      return 'Done';

      end;$BODY$

  LANGUAGE 'plpgsql' VOLATILE

CREATE OR REPLACE FUNCTION log_new(character varying)

  RETURNS character AS

$BODY$

      declare s varchar(128);

      begin

      select current_role into s;

      insert into loghist (user_name, hist_date, action) values(s , NOW(), $1 );

      return 'Logged';

      end;$BODY$

  LANGUAGE 'plpgsql' VOLATILE

Остальные функции создавались по аналогии, и приводить их в отчете нет особого смысла.

Создание видов:

CREATE OR REPLACE VIEW bill_view AS

 SELECT b.id, b.person_id, p.lastname, p.firstname, b.bill_date, b.job_id, j.task, j.price, j.worker, j.job_date, j.completed

   FROM bill b, person p, job_view j

  WHERE p.id = b.person_id AND j.id = b.job_id;

CREATE OR REPLACE VIEW job_view AS

 SELECT j.id, t.name AS task, t.price, w.name AS worker, j.job_date, j.completed

   FROM job j, task t, workers w

  WHERE j.task_id = t.id AND j.worker_id = w.id

  ORDER BY j.task_id;

CREATE OR REPLACE VIEW log_view AS

 SELECT loghist.hist_date, loghist.user_name, loghist.action

   FROM loghist

  ORDER BY loghist.hist_date, loghist.user_name;

CREATE OR REPLACE VIEW person_score AS

 SELECT p.firstname, p.lastname, person_billed(p.id) - person_paid(p.id) AS score

   FROM person p

  ORDER BY person_billed(p.id) - person_paid(p.id) DESC;

CREATE OR REPLACE VIEW workers_score AS

 SELECT w.name AS worker, worker_score(w.id) AS score

   FROM workers w

  ORDER BY worker_score(w.id) DESC;

Часть видов не показана, так как они создавались аналогично и приводить их отдельно нет смысла.

Пример выдачи прав на выполнение функций:

GRANT EXECUTE ON FUNCTION bill_new(integer, date, integer) TO GROUP "Operator";

GRANT EXECUTE ON FUNCTION bill_new(integer, date, integer) TO GROUP "Administrator";

Индексы:

CREATE INDEX task_id_ind

  ON job

  USING hash

  (task_id);

CREATE INDEX worker_id_ind

  ON job

  USING hash

  (worker_id);

CREATE INDEX bill_id_ind

  ON payment

  USING hash

  (bill_id);

Вывод

В ходе работы мы освоили работу с функциями, видами и ролями в PostgreSQL. Спроектировали базу данных и реализовали для нее основные интерфейсные функции. Научились разграничивать доступ между ролями, приобрели практические навыки проектирования и разработки БД.