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

give this a try

CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER)
RETURNS VARCHAR
AS '
DECLARE  _pid             ALIAS FOR $1;  c          text;

BEGIN

SELECT decrypt(crypted_content, decode(''password''::text,
''escape''::text), ''aes''::text) into c
FROM crypto 
WHERE pid = _pid;
  RETURN c;
END;
' LANGUAGE 'plpgsql'; 




---------- Original Message -----------
From: "Moran.Michael" <Michael.Moran@IGT.com>
To: pgsql-sql@postgresql.org
Sent: Thu, 24 Mar 2005 08:41:34 -0800
Subject: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??

> Hello there,
> 
> What's the preferred and most efficient way to obtain PGCrypto encrypted
> data from a plpgsql function?
> 
> 1. Imagine the following simple table:
> CREATE TABLE crypto (
>     pid SERIAL PRIMARY KEY,
>     title  VARCHAR(50),
>     crypted_content  BYTEA
> );
> 
> 2. Now insert the following 3 rows of data:
> 
> INSERT INTO crypto VALUES (1, 'test1', encrypt( '11112222ABCD', 'password',
> 'aes'));
> INSERT INTO crypto VALUES (2, 'test2', encrypt( '22223333BCDE', 'password',
> 'aes'));
> INSERT INTO crypto VALUES (3, 'test3', encrypt( '33334444CDEF', 'password',
> 'aes'));
> 
> 3. Using the psql tool, selecting * from the crypto table yields the
> following:
> 
> # 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 for 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';
> 
> 5. When I use the above function (in the tool, psql) to get the decrypted
> contents for ID = 2, it says I get 1 row returned, but the contents are
> blank:
> 
> # select * from selectFromCrypto(1);
>  selectfromcrypto1
> -------------------
> 
> (1 row)
> 
> Notice the blank row returned... So what am I doing wrong?
> 
> I suspect it has something to do with
> converting/encoding/decoding/decrypting the BYTEA column for return... but
> what is the problem with the above Function?
> 
> I'm using PostgreSQL 7.4.6, on a Fedora Core 1 system.
> 
> Best regards and thank you very much in advance,
> Michael Moran
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
------- End of Original Message -------