Postgresql pgp multiple row decryption

Question

I have a problem. A 'message' table has a senderId, receiverId, id and text. The text is in bytea meaning it's encrypted. My issue here is that I am trying to create a procedure which would take a usersId, his private key and the key password as parameters and return him all of his messages already decrypted. So far I have this.

CREATE OR REPLACE FUNCTION readAllUserMessages( userId Users.id%TYPE,
 privateKey varchar(3000), privateKeyPassword text) 
 RETURNS TEXT AS $$
DECLARE
    msg text;
    encryptedMsg Messages.text%TYPE;
    cur CURSOR FOR SELECT * FROM messages WHERE receiverId = userId;
BEGIN
    FOR row IN cur LOOP
        encryptedMsg := row.text;
        msg :=  pgp_pub_decrypt(encryptedMsg, dearmor(privateKey),privateKeyPassword);
        RETURN msg;

    END LOOP;

END;
$$ LANGUAGE plpgsql;

which for obvious reasons returns only one row. Do you have any suggestions how this could be achieved? Sincerely

Answer 1

You need to define the function as:

RETURNS SETOF TEXT

and then return the messages row by row:

RETURN NEXT msg;