Creating SQL Functions

Question

I've created the function, but have some mistakes. I understand the logic, but can't connect it.Maybe you will be able to help me to understand it.

Given database schema:

• Contacts (user ID, fname, lname, cell, city, country) PK(user ID)

• Conversation (convID, duration) PK(convID)

• Messages (msgID, sender ID, time sent, convID, msg text, spam) PK(msgID) FK(sender ID) → Contacts(user ID) FK(convID) → Conversation(convID)

• Recipients (msgID, user ID, time read) PK(msgID,user ID) FK(msgID) → Messages(msgID) FK(user ID) → Contacts(user ID)

Question:

Create a function, called get max duration(msgID) that will calculate the duration in seconds of the conversation that the message belongs to. The duration should be found based on the following calculation: CONV DURATION = MAX(message.time read) - MIN(message.time sent) That is, for a specific conversation duration, you need to find the latest read message and the earliest sent message. You may find useful using the following temporal built-in function extract (seconds from [time])

My solution:

CREATE OR REPLACE FUNCTION get_max_duration(msgID int) 
RETURNS INTEGER

AS $$
    DECLARE msgID Integer;
         CONV_DURATION Integer;

    BEGIN
        Select Conversation.conv_ID
        FROM Conversation
        INNER JOIN Message M on Conversation.conv_ID = M.conv_ID
        WHERE time_sent = MIN(message.time_sent)
        GROUP BY Conversation.conv_ID;

        SELECT Message. msg_ID
        FROM Message
        JOIN Recipient R on Message.msg_ID = R.msg_ID
        WHERE time_read = MAX(message.time_read)
        GROUP BY Message. msg_ID;

        Select Conversation.duration
        FROM Conversation
        WHERE duration = CONV_DURATION
        GROUP BY Conversation.duration;


        SELECT CONV_DURATION = TIMESTAMP  (MAX(message.time_read) - MIN(message.time_sent));
       RETURN CONV_DURATION;
    end;

$$ LANGUAGE plpgsql;