Re: Functions + plpgsql + contrib/pgcrypto = ??

On Thu, Mar 24, 2005 at 08:40:54AM -0800, Moran.Michael wrote:
> # select * from crypto;
> id | title | crypted_content
> ----+-------+------------------------------------------------
> 1 | test1 | \307\266xI\235"\210a\363=\201\222\247\2660\215
> 2 | test2 | D\211\256E\372^Y\0259\341\215\271\030]\234\017
> 3 | test3 | 4\263\251y\006_N\343\0270I\207;\370\270\266
>
>
> Pretty straight forward, right?

[ .. ]

> Now how about doing this in a simple plpgsql Function. That's where we
> encounter problems. I want to get DECRYPTED data based on an input ID. So...
>
>
> 4. Imagine the following simple plpgsql function (note I'm trying to decrypt
> the table's encrypted BYTEA column into a decrypted VARCHAR upon return):
>
>
> CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER)
> RETURNS VARCHAR
> AS '
> DECLARE
> crypto_cursor CURSOR (input INTEGER) FOR SELECT
> encode(decrypt(crypted_content, decode(''password''::text,
> ''escape''::text), ''aes''::text), ''escape''::text) FROM crypto WHERE id =
> input;
> crypto crypto.crypted_content%TYPE;
> tid ALIAS FOR $1;
>
> BEGIN
> OPEN crypto_cursor( tid );
> LOOP
> FETCH crypto_cursor INTO crypto;
> EXIT WHEN NOT FOUND;
> END LOOP;
> CLOSE crypto_cursor;
> RETURN ( encode(crypto, ''escape''::text)::VARCHAR );
> END;
> ' LANGUAGE 'plpgsql';

1. Why the cursor? I'd do 'select decrypt() into crypto .. '

2. After the loop, crypto is guaranteed to be null.

3. Why encode() 2 times?

--
marko