-- FUNCTION: public.judge_chat_nearness()

-- DROP FUNCTION public.judge_chat_nearness();

CREATE FUNCTION public.judge_chat_nearness()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $$     
	 BEGIN    
	 NEW.recent = age(now(), (select max(time_send) from chat where uid = NEW.uid)) < interval '1m'; 
	 RETURN NEW; 
	 END;
$$;
COMMENT ON FUNCTION public.judge_chat_nearness()
    IS 'Show the time if not near the last message';


CREATE TRIGGER chat_create
    BEFORE INSERT
    ON public.chat
    FOR EACH ROW
    EXECUTE PROCEDURE public.judge_chat_nearness();

COMMENT ON TRIGGER chat_create ON public.chat
    IS 'Is it necessary to display the send time for customer?';


CREATE FUNCTION chat_history (uid INTEGER, last_cid INTEGER DEFAULT 0)
AS
$$
SELECT id, sender, type, data, TO_CHAR(time_send, 'YYYY-MM-DD HH24:MI:SS') AS time
FROM chat
WHERE (uid = $1) AND (id > $2);
$$
LANGUAGE 'sql';

INSERT INTO chat (uid, pid, sender, data, type)  VALUES ($1, $3, E'client', $2, E'text') 
RETURNING sender, type, data, to_char(time_send, E'YYYY-MM-DD HH24:MI:SS') AS time, recent