10.1. Requêtes SQL
10.1.1. Before Starting
Before proceeding to actions described further, SQL-queries for this step should be performed.
10.1.2. Creating Function to Save Sent Message to a Database
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; -- Outgoing mail;
var_comment_remark text; -- Comment
var_object_id integer; -- Ticket ID;
BEGIN
var_object_id := COALESCE(object_id, f_register_object, "1001.object_id");
/* Body */
var_comment_remark:= COALESCE(pdb_email_body,'Body is empty.');
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;
/* Comment type ID to be returned */
r_comment_type_id := var_comment_type_id;
/* Ticket ID to be returned */
r_object_id := var_object_id;
RETURN QUERY
SELECT
r_object_id,
r_comment_id,
r_comment_type_id;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
10.1.3. Creating a Function to Increase the Level of Quoted Text
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;
10.1.4. Creating a Function that Forms the Body of the Message when Redirected
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; -- Date of original message
var_time time; -- Time
var_from text; -- Sender of original message
var_theme text; -- Original message subject
var_to text; -- Message receiver
var_orig_text text; -- Original message text
var_signature text; -- Signature
var_new_text text; -- New text
var_result text; -- Result
BEGIN
/* Original message date */
var_date := (SELECT dttmcr FROM t_comments WHERE comment_id = var_comment_id);
/* Original message time */
var_time := (SELECT dttmcr::time FROM t_comments WHERE comment_id = var_comment_id);
/* Sender of original message */
var_from := (SELECT comment_from FROM t_comments WHERE comment_id = var_comment_id);
/* Original message subject */
var_theme := (SELECT comment_subject FROM t_comments WHERE comment_id = var_comment_id);
/* Message receiver */
var_to := (SELECT comment_to FROM t_comments WHERE comment_id = var_comment_id);
/* Original message text */
var_orig_text := (SELECT comment_text FROM t_comments WHERE comment_id = var_comment_id);
/* Signature */
var_signature := (SELECT user_signature FROM t_users WHERE user_id = pdb_userid);
SELECT
'Begin forwarded message:'||
chr(10)||chr(10)||
'From: '||COALESCE(var_from, 'sender unknown')||chr(10)||
'Subject: '||COALESCE(var_theme, '-')||chr(10)||
'Date: '||COALESCE(var_date::text, 'date unknown')||chr(10)||
'To: '||COALESCE(var_to, '-')||
chr(10)||chr(10)||
COALESCE(var_orig_text, 'Forwarded message body is empty.')
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;
10.1.5. Creating a Function that Forms the Body of a Message when Replying
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; -- Date of original message
var_time time; -- Time
var_from text; -- Sender of original message
var_orig_text text; -- Original message text
var_signature text; -- Signature
var_new_text text; -- New text
var_result text; -- Result
BEGIN
/* Date of original message */
var_date := (SELECT dttmcr::date FROM t_comments WHERE comment_id = var_comment_id);
/* Original message time */
var_time := (SELECT dttmcr::time FROM t_comments WHERE comment_id = var_comment_id);
/* Sender of original message */
var_from := (SELECT comment_from FROM t_comments WHERE comment_id = var_comment_id);
/* Original message text */
var_orig_text := (SELECT comment_text FROM t_comments WHERE comment_id = var_comment_id);
/* Signature */
var_signature := (SELECT user_signature FROM t_users WHERE user_id = pdb_userid);
/* Result */
SELECT
COALESCE(var_date::text, 'date unknown')||', at '||COALESCE(var_time::text, 'time unknown')||', '||COALESCE(var_from, 'sender unknown')||' wrote:'||
chr(10)||chr(10)||
COALESCE(var_orig_text, 'Original message body is empty.')
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;
10.1.6. Next