Re: 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(at)contactbda(dot)com]
Sent: Thu 3/24/2005 9:14 AM
To: Moran.Michael; pgsql-sql(at)postgresql(dot)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(dot)Moran(at)IGT(dot)com>
To: pgsql-sql(at)postgresql(dot)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 -------