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

returns setof text ...

Craig Ringer
– Craig Ringer

2015-11-14 14:51:24 +00:00
Commented Nov 14, 2015 at 14:51 — Craig Ringer
– Craig Ringer, Commented Nov 14, 2015 at 14:51

A. Scherbaum · Accepted Answer · 2015-11-14 18:08:38Z

1

You need to define the function as:

RETURNS SETOF TEXT

and then return the messages row by row:

RETURN NEXT msg;

answered Nov 14, 2015 at 18:08

A. Scherbaum

8555 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FancyPants Over a year ago

So simple, yet so useful. +experience for me and +rep for you. Thanks !

Collectives™ on Stack Overflow

Postgresql pgp multiple row decryption

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related