Thread: 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\2152 | 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
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 -------
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 -------
I would change the return type to TEXT, I believe your original example had it as a varchar and I didn't change it. Also, I believe that "under the hood" text does equal varchar. Glad I could help Jim ---------- Original Message ----------- From: "Moran.Michael" <Michael.Moran@IGT.com> To: jim@contactbda.com, pgsql-sql@postgresql.org Sent: Thu, 24 Mar 2005 09:43:18 -0800 Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ?? > 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 ------- > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly ------- End of Original Message -------
Jim, I was thinking... In your excellent solution below, we select/decrypt and return the crypto column based on an input Integer ID. This is good. But what if we wanted to do the reverse? That is, what if I want to select an ID based on input crypto data? i.e., given my original table data way below: 1. Pass into the function the decrypted string ''22223333BCDE' 2. The function looks up the ID that matchines the encrypted value of '22223333BCDE' ... 3. Finally: the matching ID of 2 is returned. How would that change your solution below? Thanks again in advance. -Michael Moran _____ From: Jim Buttafuoco [mailto:jim@contactbda.com] Sent: Thu 3/24/2005 9:58 AM To: Moran.Michael; Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ?? I would change the return type to TEXT, I believe your original example had it as a varchar and I didn't change it. Also, I believe that "under the hood" text does equal varchar. Glad I could help Jim ---------- Original Message ----------- From: "Moran.Michael" <Michael.Moran@IGT.com> To: jim@contactbda.com, pgsql-sql@postgresql.org Sent: Thu, 24 Mar 2005 09:43:18 -0800 Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ?? > 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 <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 ------- > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly ------- End of Original Message -------
I figured it out: Here's how to get an ID from the table below, based on input crypto data (this essentially is the reverse of Jim's excellent cryto-to-ID solution below): -- -- selectFromCrypto.sql -- -- Purpose: select from crypto table based on input crypto data in VARCHAR format. -- Returns the corresponding ID (INTEGER) value for the crypto data. -- CREATE OR REPLACE FUNCTION selectFromCrypto2(VARCHAR) RETURNS INTEGER AS ' DECLARE _crypt_data ALIAS FOR $1; ret INTEGER; BEGIN SELECT id INTO ret FROM crypto WHERE encode( decrypt(crypted_content, decode(''password''::text, ''escape''::text), ''aes''::text), ''escape''::text ) = _crypt_data; RETURN ( ret ); END; ' LANGUAGE 'plpgsql'; Thanks, all. This postgresql forum rocks! Michael Moran _____ From: Moran.Michael Sent: Thu 3/24/2005 10:59 AM To: jim@contactbda.com; pgsql-sql@postgresql.org Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ?? Jim, I was thinking... In your excellent solution below, we select/decrypt and return the crypto column based on an input Integer ID. This is good. But what if we wanted to do the reverse? That is, what if I want to select an ID based on input crypto data? i.e., given my original table data way below: 1. Pass into the function the decrypted string ''22223333BCDE' 2. The function looks up the ID that matchines the encrypted value of '22223333BCDE' ... 3. Finally: the matching ID of 2 is returned. How would that change your solution below? Thanks again in advance. -Michael Moran _____ From: Jim Buttafuoco [mailto:jim@contactbda.com <mailto:jim@contactbda.com> ] Sent: Thu 3/24/2005 9:58 AM To: Moran.Michael; Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ?? I would change the return type to TEXT, I believe your original example had it as a varchar and I didn't change it. Also, I believe that "under the hood" text does equal varchar. Glad I could help Jim ---------- Original Message ----------- From: "Moran.Michael" <Michael.Moran@IGT.com> To: jim@contactbda.com, pgsql-sql@postgresql.org Sent: Thu, 24 Mar 2005 09:43:18 -0800 Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ?? > 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 <mailto:jim@contactbda.com> <mailto:jim@contactbda.com <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 ------- > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly ------- End of Original Message ------- ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match