Re: Funtions + plpgsql + contrib/pgcrypto = ?? - Mailing list pgsql-sql
From | Moran.Michael |
---|---|
Subject | Re: Funtions + plpgsql + contrib/pgcrypto = ?? |
Date | |
Msg-id | B27C8914860EE24E865D189A3735EA53100946@lasexch03.is.ad.igt.com Whole thread Raw |
In response to | Funtions + plpgsql + contrib/pgcrypto = ?? ("Moran.Michael" <Michael.Moran@IGT.com>) |
Responses |
Re: Funtions + plpgsql + contrib/pgcrypto = ??
|
List | pgsql-sql |
Thank you, Jim. You rock! This worked.... although I'm a bit confused: Your function below says it returns VARCHAR, yet the variable that holds the contents of my SELECT which we ultimately return is of type TEXT. When I return the TEXT variable, it works. Does this mean TEXT = VARCHAR in plpgsql Functions? Yet if you cast the TEXT to VARCHAR in your RETURN line below (because, it seems logical to try to match the declared return type)... it fails So, this works: return c; This doesn't: return c::VARCHAR; I always thought matching my return type to my funtion's RETURN declaration is logical... but now I know that if want a VARCHAR, I gotta manipulate it as a TEXT within my function when using PGCrypto. Any idea why? Thank again, Jim! _____ From: Jim Buttafuoco [mailto:jim@contactbda.com] Sent: Thu 3/24/2005 9:14 AM To: Moran.Michael; pgsql-sql@postgresql.org Subject: Re: [SQL] 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 -------