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

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(at)contactbda(dot)com; pgsql-sql(at)postgresql(dot)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(at)contactbda(dot)com <mailto:jim(at)contactbda(dot)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(dot)Moran(at)IGT(dot)com>
To: jim(at)contactbda(dot)com, pgsql-sql(at)postgresql(dot)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(at)contactbda(dot)com
<mailto:jim(at)contactbda(dot)com>
<mailto:jim(at)contactbda(dot)com <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 -------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)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