Database Reference
In-Depth Information
CREATE or REPLACE FUNCTION send_message(
IN i_from_user text, IN i_to_user text, IN
i_message text,
OUT status int, OUT message text )
AS $$
BEGIN
PERFORM 1 FROM user_info
WHERE username = i_to_user
AND (NOT friends_only OR friend_list @>
ARRAY[i_from_user]);
IF NOT FOUND THEN
status = 400;
message = 'SENDING FAILED';
RETURN;
END IF;
INSERT INTO message(from_user, to_user,
msg_body, delivery_status)
VALUES (i_from_user, i_to_user, i_message,
'sent');
status = 200;
message = 'OK';
EXCEPTION
WHEN foreign_key_violation THEN
status = 500;
message = 'FAILED';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
The function used for messaging simply get messages is as follows:
CREATE or REPLACE FUNCTION get_new_messages(
IN i_username text,
OUT o_status int, OUT o_message_text text,
OUT o_from_user text, OUT o_sent_at
timestamp)
RETURNS SETOF RECORD
AS $$
