Re: Funtions + plpgsql + contrib/pgcrypto = ?? - Mailing list pgsql-sql

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 -------