Все SQL-запросы

CREATE DATABASE support; -- ВНИМАНИЕ! первым выполняется отдельно запрос на создание базы, затем необходимо подключиться к созданной базе данных support и выполнить все остальные запросы ниже.

CREATE TABLE t_users

(

user_id serial NOT NULL, -- ID записи

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Дата и время создания записи

dttmup timestamp with time zone, -- Дата и время изменения записи

dttmcl timestamp with time zone, -- Дата и время закрытия записи

userid integer, -- ID пользователя, создавшего запись

user_name text, -- Имя пользователя

user_surname text, -- Фамилия пользователя

user_login text, -- Имя учетной записи

user_password text, -- Имя учетной записи

user_signature text, -- Подпись пользователя

CONSTRAINT pk_t_users_user_id PRIMARY KEY (user_id)

);

COMMENT ON TABLE t_users

IS 'Таблица для хранения информации о пользователях';

COMMENT ON COLUMN t_users.user_id IS 'ID записи';

COMMENT ON COLUMN t_users.dttmcr IS 'Дата и время создания записи';

COMMENT ON COLUMN t_users.dttmup IS 'Дата и время изменения записи';

COMMENT ON COLUMN t_users.dttmcl IS 'Дата и время закрытия записи';

COMMENT ON COLUMN t_users.userid IS 'ID пользователя, создавшего запись';

COMMENT ON COLUMN t_users.user_name IS 'Имя пользователя';

COMMENT ON COLUMN t_users.user_surname IS 'Фамилия пользователя';

COMMENT ON COLUMN t_users.user_login IS 'Имя учетной записи';

COMMENT ON COLUMN t_users.user_password IS 'Имя учетной записи';

COMMENT ON COLUMN t_users.user_signature IS 'Подпись пользователя';

CREATE OR REPLACE VIEW vw_users AS 

SELECT t_users.user_id,

t_users.dttmcr,

t_users.dttmup,

t_users.dttmcl,

t_users.userid,

t_users.user_name,

t_users.user_surname,

(COALESCE(t_users.user_name, ''::text) || ' '::text) || COALESCE(t_users.user_surname, ''::text) AS user_fullname,

t_users.user_login,

t_users.user_password,

t_users.user_signature

FROM t_users;

CREATE TABLE t_clients

(

client_id serial NOT NULL, -- ID записи

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Дата и время создания записи

dttmup timestamp with time zone, -- Дата и время изменения записи

dttmcl timestamp with time zone, -- Дата и время закрытия записи

userid integer, -- ID пользователя, создавшего запись

client_name text, -- Имя клиента

client_surname text, -- Фамилия клиента

CONSTRAINT pk_t_clients_client_id PRIMARY KEY (client_id)

);

COMMENT ON TABLE t_clients

IS 'Таблица для хранения информации о клиентах';

COMMENT ON COLUMN t_clients.client_id IS 'ID записи';

COMMENT ON COLUMN t_clients.dttmcr IS 'Дата и время создания записи';

COMMENT ON COLUMN t_clients.dttmup IS 'Дата и время изменения записи';

COMMENT ON COLUMN t_clients.dttmcl IS 'Дата и время закрытия записи';

COMMENT ON COLUMN t_clients.userid IS 'ID пользователя, создавшего запись';

COMMENT ON COLUMN t_clients.client_name IS 'Имя клиента';

COMMENT ON COLUMN t_clients.client_surname IS 'Фамилия клиента';

CREATE OR REPLACE VIEW vw_clients AS 

SELECT t_clients.client_id,

t_clients.dttmcr,

t_clients.dttmup,

t_clients.dttmcl,

t_clients.userid,

t_clients.client_name,

t_clients.client_surname,

(COALESCE(t_clients.client_name, ''::text) || ' '::text) || COALESCE(t_clients.client_surname, ''::text) AS client_fullname

FROM t_clients;

CREATE TABLE t_status_types

(

status_type_id serial NOT NULL, -- ID записи

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Дата и время создания записи

dttmup timestamp with time zone, -- Дата и время изменения записи

dttmcl timestamp with time zone, -- Дата и время закрытия записи

userid integer, -- ID пользователя, создавшего запись

status_type_name text, -- Наименование типа статуса

CONSTRAINT pk_t_status_types_status_type_id PRIMARY KEY (status_type_id)

);

COMMENT ON TABLE t_status_types

IS 'Таблица для хранения типов статусов';

COMMENT ON COLUMN t_status_types.status_type_id IS 'ID записи';

COMMENT ON COLUMN t_status_types.dttmcr IS 'Дата и время создания записи';

COMMENT ON COLUMN t_status_types.dttmup IS 'Дата и время изменения записи';

COMMENT ON COLUMN t_status_types.dttmcl IS 'Дата и время закрытия записи';

COMMENT ON COLUMN t_status_types.userid IS 'ID пользователя, создавшего запись';

COMMENT ON COLUMN t_status_types.status_type_name IS 'Наименование типа статуса';

CREATE TABLE t_comment_types

(

comment_type_id serial NOT NULL, -- ID записи

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Дата и время создания записи

dttmup timestamp with time zone, -- Дата и время изменения записи

dttmcl timestamp with time zone, -- Дата и время закрытия записи

userid integer, -- ID пользователя, создавшего запись

comment_type_name text, -- Наименование типа комментария

CONSTRAINT pk_t_comment_types_comment_type_id PRIMARY KEY (comment_type_id)

);

COMMENT ON TABLE t_comment_types

IS 'Таблица для хранения типа комментариев';

COMMENT ON COLUMN t_comment_types.comment_type_id IS 'ID записи';

COMMENT ON COLUMN t_comment_types.dttmcr IS 'Дата и время создания записи';

COMMENT ON COLUMN t_comment_types.dttmup IS 'Дата и время изменения записи';

COMMENT ON COLUMN t_comment_types.dttmcl IS 'Дата и время закрытия записи';

COMMENT ON COLUMN t_comment_types.userid IS 'ID пользователя, создавшего запись';

COMMENT ON COLUMN t_comment_types.comment_type_name IS 'Наименование типа комментария';

INSERT INTO t_status_types (dttmcr, dttmup, userid, status_type_name) VALUES 

(now(), now(), 1, 'Заявлено'),

(now(), now(), 1, 'В обработке'),

(now(), now(), 1, 'Ответ'),

(now(), now(), 1, 'Закрыто');

INSERT INTO t_comment_types (dttmcr, dttmup, userid, comment_type_name) VALUES 

(now(), now(), 1, 'Входящее письмо'),

(now(), now(), 1, 'Исходящее письмо'),

(now(), now(), 1, 'Комментарий');

CREATE SEQUENCE t_objects_object_ref_nr_seq

INCREMENT 1

MINVALUE 1

MAXVALUE 9223372036854775807

START 300001

CACHE 1;

CREATE TABLE t_objects

(

object_id serial NOT NULL, -- ID записи

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Дата и время создания записи

dttmup timestamp with time zone, -- Дата и время изменения записи

dttmcl timestamp with time zone, -- Дата и время закрытия записи

userid integer, -- ID пользователя, создавшего запись

object_ref_nr integer DEFAULT nextval('t_objects_object_ref_nr_seq'::regclass), -- Реферативный номер обращения

object_cur_status_id integer, -- ID текущего статуса - регулируется триггером при добавлении нового статуса в таблицу t_statuses.

client_id integer, -- ID клиента - FK t_clients.client_id

object_to_id integer, -- ID ответственного сотрудника - FK t_users.user_id

object_remark text, -- Примечание к обращению

CONSTRAINT pk_t_objects_object_id PRIMARY KEY (object_id)

);

COMMENT ON TABLE t_objects

IS 'Таблица для хранения обращений';

COMMENT ON COLUMN t_objects.object_id IS 'ID записи';

COMMENT ON COLUMN t_objects.dttmcr IS 'Дата и время создания записи';

COMMENT ON COLUMN t_objects.dttmup IS 'Дата и время изменения записи';

COMMENT ON COLUMN t_objects.dttmcl IS 'Дата и время закрытия записи';

COMMENT ON COLUMN t_objects.userid IS 'ID пользователя, создавшего запись';

COMMENT ON COLUMN t_objects.object_ref_nr IS 'Реферативный номер обращения';

COMMENT ON COLUMN t_objects.object_cur_status_id IS 'ID текущего статуса - регулируется триггером при добавлении нового статуса в таблицу t_statuses.';

COMMENT ON COLUMN t_objects.client_id IS 'ID клиента - FK t_clients.client_id';

COMMENT ON COLUMN t_objects.object_to_id IS 'ID ответственного сотрудника - FK t_users.user_id';

COMMENT ON COLUMN t_objects.object_remark IS 'Примечание к обращению'; 

CREATE OR REPLACE VIEW vw_objects AS 

SELECT o.object_id,

o.dttmcr,

o.dttmup,

o.dttmcl,

o.userid,

o.object_ref_nr,

o.object_cur_status_id,

o.object_remark,

st.status_type_name,

(COALESCE(c.client_name, ''::text) || ' '::text) || COALESCE(c.client_surname, ''::text) AS client_fullname,

(COALESCE(u.user_name, ''::text) || ' '::text) || COALESCE(u.user_surname, ''::text) AS user_fullname

FROM t_objects o

LEFT JOIN t_status_types st ON o.object_cur_status_id = st.status_type_id

LEFT JOIN t_clients c USING (client_id)

LEFT JOIN t_users u ON o.object_to_id = u.user_id;

CREATE TABLE t_statuses

(

status_id serial NOT NULL,

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Дата и время создания записи

dttmup timestamp with time zone, -- Дата и время изменения записи

dttmcl timestamp with time zone, -- Дата и время закрытия записи

userid integer, -- ID пользователя, создавшего запись

object_id integer NOT NULL, -- ID обращения - FK t_objects.object_id

status_type_id integer NOT NULL, -- ID типа статуса

CONSTRAINT pk_t_statuses_status_id PRIMARY KEY (status_id)

);

COMMENT ON TABLE t_statuses

IS 'Таблица для хранения статусов';

COMMENT ON COLUMN t_statuses.dttmcr IS 'Дата и время создания записи';

COMMENT ON COLUMN t_statuses.dttmup IS 'Дата и время изменения записи';

COMMENT ON COLUMN t_statuses.dttmcl IS 'Дата и время закрытия записи';

COMMENT ON COLUMN t_statuses.userid IS 'ID пользователя, создавшего запись';

COMMENT ON COLUMN t_statuses.object_id IS 'ID обращения - FK t_objects.object_id';

COMMENT ON COLUMN t_statuses.status_type_id IS 'ID типа статуса'; 

CREATE OR REPLACE VIEW vw_statuses AS 

SELECT s.status_id,

s.dttmcr,

s.dttmup,

s.dttmcl,

s.userid,

s.object_id,

st.status_type_name

FROM t_statuses s

LEFT JOIN t_status_types st USING (status_type_id);

CREATE TABLE t_comments

(

comment_id serial NOT NULL,

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Дата и время создания записи

dttmup timestamp with time zone, -- Дата и время изменения записи

dttmcl timestamp with time zone, -- Дата и время закрытия записи

userid integer, -- ID пользователя, создавшего запись

object_id integer, -- ID обращения - FK t_objects.object_id

comment_type_id integer NOT NULL, -- ID типа комментария - FK t_comment_type.comment_type_id

comment_from text, -- Отправитель

comment_to text, -- Получатель

comment_cc text, -- Копия

comment_bcc text, -- Скрытая копия

comment_subject text, -- Тема

comment_text text, -- Тело

comment_diag_mes text, -- Диагностическое сообщение, формируемое при анализе темы письма

CONSTRAINT pk_t_comments_comment_id PRIMARY KEY (comment_id)

);

COMMENT ON TABLE t_comments

IS 'Таблица для хранения комментариев';

COMMENT ON COLUMN t_comments.dttmcr IS 'Дата и время создания записи';

COMMENT ON COLUMN t_comments.dttmup IS 'Дата и время изменения записи';

COMMENT ON COLUMN t_comments.dttmcl IS 'Дата и время закрытия записи';

COMMENT ON COLUMN t_comments.userid IS 'ID пользователя, создавшего запись';

COMMENT ON COLUMN t_comments.object_id IS 'ID обращения - FK t_objects.object_id';

COMMENT ON COLUMN t_comments.comment_type_id IS 'ID типа комментария - FK t_comment_type.comment_type_id';

COMMENT ON COLUMN t_comments.comment_from IS 'Отправитель';

COMMENT ON COLUMN t_comments.comment_to IS 'Получатель';

COMMENT ON COLUMN t_comments.comment_cc IS 'Копия';

COMMENT ON COLUMN t_comments.comment_bcc IS 'Скрытая копия';

COMMENT ON COLUMN t_comments.comment_subject IS 'Тема';

COMMENT ON COLUMN t_comments.comment_text IS 'Тело';

COMMENT ON COLUMN t_comments.comment_diag_mes IS 'Диагностическое сообщение, формируемое при анализе темы письма';

CREATE TABLE t_files

(

file_id serial NOT NULL, -- ID записи

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Дата и время создания записи

dttmup timestamp with time zone, -- Дата и время изменения записи

dttmcl timestamp with time zone, -- Дата и время закрытия записи

userid integer, -- ID пользователя, создавшего запись

comment_id integer, -- ID комментария - FK t_comments.comment_id

comment_file bytea, -- Файл

CONSTRAINT pk_t_files_file_id PRIMARY KEY (file_id)

);

COMMENT ON TABLE t_files

IS 'Таблица для хранения файлов';

COMMENT ON COLUMN t_files.file_id IS 'ID записи';

COMMENT ON COLUMN t_files.dttmcr IS 'Дата и время создания записи';

COMMENT ON COLUMN t_files.dttmup IS 'Дата и время изменения записи';

COMMENT ON COLUMN t_files.dttmcl IS 'Дата и время закрытия записи';

COMMENT ON COLUMN t_files.userid IS 'ID пользователя, создавшего запись';

COMMENT ON COLUMN t_files.comment_id IS 'ID комментария - FK t_comments.comment_id';

COMMENT ON COLUMN t_files.comment_file IS 'Файл';

CREATE OR REPLACE VIEW vw_comments AS 

SELECT c.comment_id,

c.dttmcr,

c.dttmup,

c.dttmcl,

c.userid,

c.object_id,

c.comment_type_id,

c.comment_from,

c.comment_to,

c.comment_cc,

c.comment_bcc,

c.comment_subject,

c.comment_diag_mes,

c.comment_text,

ct.comment_type_name,

CASE

WHEN (( SELECT DISTINCT t_files.comment_id

FROM t_files

WHERE t_files.comment_id = c.comment_id AND t_files.dttmcl IS NULL)) IS NULL THEN '-'::text

ELSE 'Да'::text

END AS comment_file_present

FROM t_comments c

LEFT JOIN t_comment_types ct USING (comment_type_id);

CREATE OR REPLACE FUNCTION trig_t_objects_i_new_status()

RETURNS trigger AS

$BODY$

DECLARE

var_status_id integer := 1;          -- ID статуса Заявлено;

BEGIN

INSERT INTO t_statuses (userid, object_id, status_type_id)

VALUES (NEW.userid, NEW.object_id, var_status_id);

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION trig_t_statuses_iu_status()

RETURNS trigger AS

$BODY$

BEGIN

UPDATE t_objects 

SET object_cur_status_id = NEW.status_type_id

WHERE t_objects.object_id = NEW.object_id;

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER trig_t_objects_i_new_status

AFTER INSERT

ON t_objects

FOR EACH ROW

EXECUTE PROCEDURE trig_t_objects_i_new_status();

CREATE TRIGGER trig_t_statuses_iu_status

AFTER INSERT

ON t_statuses

FOR EACH ROW

EXECUTE PROCEDURE trig_t_statuses_iu_status();

CREATE OR REPLACE VIEW vw_latest_comments AS

SELECT c.comment_id,

c.dttmcr,

c.dttmup,

c.dttmcl,

c.userid,

o.object_ref_nr,

st.status_type_name,

ct.comment_type_name,

c.comment_from,

c.comment_text,

CASE

WHEN (( SELECT DISTINCT t_files.comment_id

FROM t_files

WHERE t_files.comment_id = c.comment_id AND t_files.dttmcl IS NULL)) IS NULL THEN '-'::text

ELSE 'Да'::text

END AS comment_file_present

FROM t_comments c

LEFT JOIN t_comment_types ct USING (comment_type_id)

LEFT JOIN t_objects o USING (object_id)

LEFT JOIN t_status_types st ON o.object_cur_status_id = st.status_type_id

WHERE o.object_id IS NOT NULL AND o.dttmcl IS NULL AND c.dttmcl IS NULL

ORDER BY c.dttmcr DESC;

CREATE FUNCTION f_start_processing(

pdb_userid integer,

"1002.object_id" integer,

"1002.object_cur_status_id" integer,

var_timestamp timestamp with time zone)

RETURNS integer AS

$BODY$

DECLARE

var_status_id integer;

var_status_type_id integer:= 2;          -- ID статуса "В обработке" из таблицы t_status_types

BEGIN

/* Указываем ответственное лицо */

UPDATE t_objects

SET object_to_id = pdb_userid

WHERE object_id = "1002.object_id";

/* Вводим статус В обработке (2) по обращению, если текущий статус Заявлено (1) */

IF "1002.object_cur_status_id" = 1 THEN

/* Если в функцию передается время, вставить значения + время */

IF var_timestamp IS NOT NULL THEN

INSERT INTO t_statuses (dttmcr, userid, object_id, status_type_id)

VALUES (var_timestamp, pdb_userid, "1002.object_id", var_status_type_id)

RETURNING status_id INTO var_status_id;

ELSE

/* Если в функцию время не передается, вставить просто значения */

INSERT INTO t_statuses (userid, object_id, status_type_id)

VALUES (pdb_userid, "1002.object_id", var_status_type_id)

RETURNING status_id INTO var_status_id;

END IF;

END IF;          

/* Возвращаем ID записи из таблицы support.objects */

RETURN var_status_id;

END;

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION f_get_filename(_filename bytea)

RETURNS text AS

$BODY$

declare

len integer; 

begin

len := "position"(_filename, E'\\000'::bytea)-1;

if len > 0 then

return convert_from("substring"(_filename, 1, len ), 'WIN1251');

end if;

return NULL::text;

end;

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE VIEW vw_files AS 

SELECT t_files.file_id,

t_files.dttmcr,

t_files.dttmup,

t_files.dttmcl,

t_files.userid,

t_files.comment_id,

f_get_filename(t_files.comment_file) AS filename

FROM t_files;

CREATE OR REPLACE FUNCTION f_get_file("1210.file_id" integer)

RETURNS SETOF bytea AS

$BODY$

BEGIN

RETURN QUERY

SELECT comment_file

FROM t_files

WHERE file_id = $1;

END;

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION f_upload_file(

pdb_userid integer,

"1202.comment_id" integer,

pdb_file_data bytea)

RETURNS boolean AS

$BODY$

BEGIN

INSERT INTO t_files (userid, comment_id, comment_file)

VALUES (pdb_userid, "1202.comment_id", pdb_file_data);

RETURN true;

END

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION f_close_file("1210.file_id" integer)

RETURNS boolean AS

$BODY$

BEGIN

UPDATE t_files

SET dttmcl = now() WHERE t_files.file_id = "1210.file_id";

RETURN true;

END

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE TYPE parsed_email_subject AS

(subject_type_code integer,

object_id integer,

rest_email_subject text,

diag_mes text);

CREATE OR REPLACE FUNCTION f_parse_subject(email_subject text)

RETURNS parsed_email_subject AS

$BODY$

DECLARE

left_bracket text:= '[';

right_bracket text:= ']';

number_sign text:= '#';

subject_type_code int;                                        -- Код типа субъекта

subject_type_for_check text;                              -- Код типа субъекта для проверки, является ли он числовым значением

subject_code_is_present boolean;                    -- Наличие кода типа субъекта в таблице subject_types;

object_id integer;                                                  -- Номер объекта

object_ref_for_check text;                              -- Номер объекта для проверки, является ли он числовым значением

object_ref_is_present boolean;                    -- Наличие номера объекта в таблице objects;

rest_email_subject text;                              -- Остальная часть темы письма

pos_left_bracket int;                                        -- Позиция левой скобки

pos_right_bracket int;                                        -- Позиция правой скобки

pos_number_sign int;                                        -- Позиция знакая номера

email_subject_length int;                              -- Длина темы письма

parsed_email_subject parsed_email_subject;          -- Разбитая тема письма

diag_mes text;                                                            -- Диагностическое сообщение

BEGIN

/* Определяем позиции необходимых знаков */          

pos_left_bracket := position(left_bracket in email_subject);                    -- Определение позиции левой скобки

pos_number_sign := position(number_sign in email_subject);                    -- Определение позиции знака номера

pos_right_bracket := position(right_bracket in email_subject);                    -- Определение позиции правой скобки

email_subject_length := char_length(email_subject);                              -- Определение длины темы

/* Эта часть кода выполняется, если в теме присутствуют все три знака */

IF          pos_left_bracket <> 0                                                            -- Если присутствуют левая скобка

AND pos_right_bracket <> 0                                         -- и правая скобка

AND pos_number_sign <> 0                                                  -- а также знак номера

AND pos_number_sign BETWEEN pos_left_bracket AND pos_right_bracket                    -- Последовательность: левая скобка, знак номера, правая скобка

THEN

/* Номер объекта для проверки */

object_ref_for_check:=

trim(substring(email_subject from (pos_number_sign + 1 ) for (pos_right_bracket - pos_number_sign - 1)));

/* Определяем, присутствует ли номер объекта в таблице objects */

IF f_is_integer(object_ref_for_check) THEN

object_ref_is_present:= (SELECT COUNT(*) FROM t_objects WHERE t_objects.object_ref_nr = object_ref_for_check::integer) != 0;

END IF;

/* Номер объекта является целым числовым значением и присутствует в таблице objects */

IF object_ref_for_check IS NOT NULL AND f_is_integer(object_ref_for_check) AND object_ref_is_present THEN

object_id:= (SELECT t_objects.object_id FROM t_objects WHERE t_objects.object_ref_nr = object_ref_for_check::integer);

rest_email_subject:=trim(substring(email_subject from (pos_right_bracket + 1) for (email_subject_length + 1 - pos_right_bracket)));

diag_mes:= '0 - Номер объекта определен';

/* Номер объекта не определен */

ELSE rest_email_subject:= email_subject;

diag_mes:= '1 - Номер объекта не определен';

END IF;

/* Тему письма невозможно разобрать согласно правилам */

ELSE rest_email_subject:= email_subject;

diag_mes:= '2 - Тему письма невозможно разобрать согласно правилам';

END IF;

SELECT subject_type_code, object_id, rest_email_subject, diag_mes

INTO parsed_email_subject.subject_type_code, parsed_email_subject.object_id, parsed_email_subject.rest_email_subject, parsed_email_subject.diag_mes;          

RETURN parsed_email_subject;

END

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION f_is_integer(text)

RETURNS boolean AS

$BODY$

SELECT $1 ~ '^[0-9]+$'

$BODY$

LANGUAGE sql VOLATILE;

CREATE OR REPLACE FUNCTION f_update_status(

pdb_userid integer,

"1001.object_id" integer,

"1001.object_cur_status_id" integer,

"1202.comment_id" integer,

comment_type_id integer)

RETURNS boolean AS

$BODY$

DECLARE

var_timestamp timestamp with time zone := (SELECT now() - interval '1 second');          -- Передается в функцию f_start_processing для создания разницы в 1 секунду между статусом В обработке и Ответ;

BEGIN

/* Если тип комментария - Исходящее письмо (2), 

а также текущий статус - В обработке (2), выставить статус Ответ (3) */

IF comment_type_id IN (2) AND "1001.object_cur_status_id" IN (2) THEN

INSERT INTO t_statuses (userid, object_id, status_type_id)

VALUES (pdb_userid, "1001.object_id", 3);

/* Если тип комментария - Исходящее письмо (2), а также текущий статус - Принято (1), 

выставить cначала статус В обработке (2) и указать ответственное лицо, затем выставить статус Ответ (3) */

ELSIF comment_type_id IN (2) AND "1001.object_cur_status_id" IN (1) THEN

PERFORM f_start_processing (pdb_userid, "1001.object_id", "1001.object_cur_status_id", var_timestamp);

INSERT INTO t_statuses (userid, object_id, status_type_id)

VALUES (pdb_userid, "1001.object_id", 3);

/* Если тип комментария - Входящее письмо (1) и 

текущий статус  - Ответ (3), выставить статус В обработке (2) */

ELSIF comment_type_id IN (1) AND "1001.object_cur_status_id" IN (3) THEN

INSERT INTO t_statuses (userid, object_id, status_type_id)

VALUES (pdb_userid, "1001.object_id", 2);

END IF;

RETURN true;

END;

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION f_email_in_to_comments(

pdb_userid integer,

pdb_email_uid integer,

pdb_email_date timestamp with time zone,

pdb_email_from text,

pdb_email_to text,

pdb_email_cc text,

pdb_email_subject text,

pdb_email_mime text,

pdb_email_body text)

RETURNS integer AS

$BODY$

DECLARE

entry_id integer;                                                            -- ID новой записи в таблицу comments

var_comment_type_id integer:= 1;                                        -- Входящее письмо

var_comment_remark text;                                                  -- Примечание к комментарию

var_object_id integer;                                                            -- ID объекта

parsed_email_subject parsed_email_subject;                              -- Разобранная тема письма

var_comment_diag_mes text;                                                  -- Диагностическое сообщение, формируемое при анализе темы письма

var_object_cur_status_id integer;                                        -- ID текущего статуса дела

temp_id integer;

BEGIN

/* Разбирает тему письма */

parsed_email_subject:= f_parse_subject(pdb_email_subject);

/* Формирует примечание к комментарию */

var_comment_remark:= COALESCE(pdb_email_body,'Тело письма не содержит текст.');

/* Присваевает номер объекта */

var_object_id:= parsed_email_subject.object_id;

/* Определяет диагностическое сообщение после разбора темы */

var_comment_diag_mes:= parsed_email_subject.diag_mes;

/* Номер объекта определен и объект с указанным в теме письма номером не закрыт */

IF var_object_id IS NOT NULL AND (SELECT dttmcl FROM t_objects WHERE object_id = var_object_id) IS NULL THEN

INSERT INTO t_comments(dttmcr, userid, object_id, comment_type_id, comment_text, comment_from, comment_to, comment_cc, comment_subject, comment_diag_mes)

VALUES(pdb_email_date, pdb_userid, var_object_id, var_comment_type_id, var_comment_remark, pdb_email_from, pdb_email_to, pdb_email_cc, pdb_email_subject, var_comment_diag_mes)

RETURNING comment_id INTO entry_id;           

SELECT object_cur_status_id FROM t_objects WHERE object_id = var_object_id INTO var_object_cur_status_id;

PERFORM f_update_status (pdb_userid, var_object_id, var_object_cur_status_id, entry_id, var_comment_type_id); 

/* Номер объекта не определен или объект с указанным в теме письма номером закрыт */

ELSE

INSERT INTO t_comments(dttmcr, userid, comment_type_id, comment_text, comment_from, comment_to, comment_cc, comment_subject, comment_diag_mes)

VALUES(pdb_email_date, pdb_userid, var_comment_type_id, var_comment_remark, pdb_email_from, pdb_email_to, pdb_email_cc, pdb_email_subject, var_comment_diag_mes)

RETURNING comment_id INTO entry_id;

END IF;

RETURN entry_id;

END;

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION f_save_files(

pdb_userid integer,

pdb_email_file bytea,

pdb_docum_file bytea,

f_email_in_to_comments integer,

r_comment_id integer)

RETURNS boolean AS

$BODY$

DECLARE

BEGIN

IF f_email_in_to_comments IS NOT NULL THEN

INSERT INTO t_files(userid, comment_id, comment_file)

VALUES (pdb_userid, f_email_in_to_comments, pdb_email_file);

ELSE

INSERT INTO t_files(userid, comment_id, comment_file)

VALUES (pdb_userid, r_comment_id, pdb_docum_file);

END IF;

RETURN true;

END

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION f_register_object(

pdb_userid integer,

"1005.comment_id" integer,

"1005.client_id" integer,

"1005.object_to_id" integer,

"1005.object_remark" text)

RETURNS SETOF integer AS

$BODY$

DECLARE

r_object_id integer;                                        -- ID объекта после добавления;

BEGIN

/* Выполняется при регистрации обращения на основании входящего электронного письма */

IF "1005.comment_id" IS NOT NULL THEN

/* Создает обращение */

INSERT INTO t_objects (userid, client_id, object_to_id, object_remark)

VALUES (pdb_userid, "1005.client_id", "1005.object_to_id", "1005.object_remark")

RETURNING object_id INTO r_object_id;

/* Прикрепляет письмо к созданному обращению */

UPDATE t_comments

SET object_id = r_object_id

WHERE comment_id = "1005.comment_id";

END IF;          

/* Возвращает ID нового обращения */

RETURN QUERY

SELECT r_object_id;

END

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION f_email_out_to_comments(

IN pdb_userid integer,

IN pdb_email_uid integer,

IN pdb_email_date timestamp with time zone,

IN pdb_email_from text,

IN pdb_email_to text,

IN pdb_email_cc text,

IN pdb_email_bcc text,

IN pdb_email_subject text,

IN pdb_email_body text,

IN object_id integer,

IN f_register_object integer,

IN "1001.object_id" integer)

RETURNS TABLE(r_object_id integer, r_comment_id integer, r_comment_type_id integer) AS

$BODY$

DECLARE

var_comment_type_id integer:= 2;                    -- Исходящее письмо;

var_comment_remark text;                              -- Текст к комментарию;

var_object_id integer;                              -- ID обращения;

BEGIN

var_object_id := COALESCE(object_id, f_register_object, "1001.object_id");

/* Формирует текст комментария */

var_comment_remark:= COALESCE(pdb_email_body,'Тело письма не содержит текст.');

INSERT INTO t_comments(dttmcr, userid, object_id, comment_type_id, comment_text, comment_from, comment_to, comment_cc, comment_subject, comment_bcc)

VALUES(pdb_email_date, pdb_userid, var_object_id, var_comment_type_id, var_comment_remark, pdb_email_from, pdb_email_to, pdb_email_cc, pdb_email_subject, pdb_email_bcc)           

RETURNING comment_id INTO r_comment_id;

/* Формирует ID типа комментария для возврата */

r_comment_type_id := var_comment_type_id;

/* Формиурет ID обращения для возврата */

r_object_id := var_object_id;

RETURN QUERY

SELECT

r_object_id,

r_comment_id,

r_comment_type_id;

END;

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION f_increase_quote(orig_text text)

RETURNS text AS

$BODY$

DECLARE

result text;

BEGIN

SELECT '> '||replace(orig_text, chr(10), chr(10)||'> ')

INTO result;

RETURN result;

END

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION f_body_forward(

var_text text,

pdb_userid integer,

var_comment_id integer)

RETURNS text AS

$BODY$

DECLARE

var_date timestamp;                    -- Дата оригинального сообщения

var_time time;                              -- Время

var_from text;                              -- Отправитель оригинального сообщения

var_theme text;                    -- Оригинальная тема сообщения

var_to text;                              -- Получатель сообщения

var_orig_text text;                    -- Текст оригинального сообщения

var_signature text;                    -- Подпись

var_new_text text;                    -- Новый текст

var_result text;                    -- Результат

BEGIN

/* Формирует дату оригинального сообщения */

var_date := (SELECT dttmcr FROM t_comments WHERE comment_id = var_comment_id);

/* Формирует время оригинального сообщения */

var_time := (SELECT dttmcr::time FROM t_comments WHERE comment_id = var_comment_id);

/* Формирует отправителя оригинального сообщения */

var_from := (SELECT comment_from FROM t_comments WHERE comment_id = var_comment_id);

/* Формирует тему оригинального сообщения */

var_theme := (SELECT comment_subject FROM t_comments WHERE comment_id = var_comment_id);

/* Формирует получателя оригинального сообщения */

var_to := (SELECT comment_to FROM t_comments WHERE comment_id = var_comment_id);

/* Формирует текст оригинального сообщения */

var_orig_text := (SELECT comment_text FROM t_comments WHERE comment_id = var_comment_id);

/* Формирует подпись */

var_signature := (SELECT user_signature FROM t_users WHERE user_id = pdb_userid);

SELECT

'Начало переадресованного сообщения:'||

chr(10)||chr(10)||

'От: '||COALESCE(var_from, 'неизвестный отправитель')||chr(10)||

'Тема: '||COALESCE(var_theme, '-')||chr(10)||

'Дата и время: '||COALESCE(var_date::text, 'неизвестная дата и время')||chr(10)||

'Кому: '||COALESCE(var_to, '-')||

chr(10)||chr(10)||

COALESCE(var_orig_text, 'Оригинальное письмо не содержит текст.')

INTO var_new_text;

SELECT

COALESCE(var_text, '')||

chr(10)||chr(10)||chr(10)||

COALESCE(var_signature, '')||

chr(10)||chr(10)||chr(10)||

f_increase_quote(var_new_text)

INTO var_result; 

RETURN var_result;

END

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION f_body_reply(

var_text text,

pdb_userid integer,

var_comment_id integer)

RETURNS text AS

$BODY$

DECLARE

var_date date;                    -- Дата оригинального сообщения

var_time time;                    -- Время

var_from text;                    -- Отправитель оригинального сообщения

var_orig_text text;          -- Текст оригинального сообщения

var_signature text;          -- Подпись

var_new_text text;          -- Новый текст

var_result text;          -- Результат

BEGIN

/* Формирует дату оригинального сообщения */

var_date := (SELECT dttmcr::date FROM t_comments WHERE comment_id = var_comment_id);

/* Формирует время оригинального сообщения */

var_time := (SELECT dttmcr::time FROM t_comments WHERE comment_id = var_comment_id);

/* Формирует отправителя оригинального сообщения */

var_from := (SELECT comment_from FROM t_comments WHERE comment_id = var_comment_id);

/* Формирует текст оригинального сообщения */

var_orig_text := (SELECT comment_text FROM t_comments WHERE comment_id = var_comment_id);

/* Формирует подпись */

var_signature := (SELECT user_signature FROM t_users WHERE user_id = pdb_userid);

/* Формирует результат */

SELECT

COALESCE(var_date::text, 'Неизвестная дата')||', в '||COALESCE(var_time::text, 'неизвестное время')||', '||COALESCE(var_from, 'неизвестный отправитель')||' написал(а):'||

chr(10)||chr(10)||

COALESCE(var_orig_text, 'Оригинальное письмо не содержит текст.') 

INTO var_new_text;

SELECT

COALESCE(var_text, '')||

chr(10)||chr(10)||chr(10)||

COALESCE(var_signature, '')||

chr(10)||chr(10)||chr(10)||

f_increase_quote(var_new_text)

INTO var_result; 

RETURN var_result;

END

$BODY$

LANGUAGE plpgsql VOLATILE;