Function to convert from TEXT to BYTEA?

D. Dante Lorenso wrote:

All,

I want to use the ENCRYPT and DECRYPT functions from contrib, but they
require inputs of BYTEA.

My data is in VARCHAR and TEXT fields and when I try to use the contrib
functions, they complain about wrong datatypes. Is there a string
function or something that will take a VARCHAR or TEXT input and output
a BYTEA so that I can use that as input for the ENCRYPT/DECRYPT functions?

I know about creating a CAST from VARCHAR to BYTEA, but the problem with
a CAST is that it doesn't port to other database servers when I do a
dump and restore.

Doesn't it?
Hmm... seems to dump for me in 8.2

That forces me to manually have to recreate the cast
each time a new database is set up and usually that's the step that gets
forgotten.

Surely you have a script that creates your databases for you?

Is there a function that will do what I want to convert the datatype
without having to create a CAST that PostgreSQL doesn't have natively?
How else are you supposed to use the ENCRYPT and DECRYPT functions?

With actual bytea types?

Anyway this will convert for you - PG can get from an unknown quoted
literal to bytea just fine.

CREATE FUNCTION text2bytea(text) RETURNS bytea AS $_$
DECLARE
b bytea;
BEGIN
EXECUTE 'SELECT ' || quote_literal($1) || '::bytea' INTO b;
RETURN b;
END
$_$
LANGUAGE plpgsql;

And here's the cast definition that goes with it

CREATE CAST (text AS bytea) WITH FUNCTION public.text2bytea(text);

HTH
--
Richard Huxton
Archonet Ltd

I think decode('your string', 'escape') might be what you need.
It returns bytea when 'your string' is type text or varchar.

TJ
http://www.gnova.com

Richard Huxton wrote:

D. Dante Lorenso wrote:

I want to use the ENCRYPT and DECRYPT functions from contrib, but they
require inputs of BYTEA.

My data is in VARCHAR and TEXT fields and when I try to use the
contrib functions, they complain about wrong datatypes. Is there a
string function or something that will take a VARCHAR or TEXT input
and output a BYTEA so that I can use that as input for the
ENCRYPT/DECRYPT functions?

I know about creating a CAST from VARCHAR to BYTEA, but the problem
with a CAST is that it doesn't port to other database servers when I
do a dump and restore.

Doesn't it?
Hmm... seems to dump for me in 8.2

My CAST was defined as follows:

CREATE CAST (VARCHAR AS BYTEA) WITHOUT FUNCTION;

Tom explains why that does NOT dump and restore with my database here:

http://archives.postgresql.org/pgsql-general/2007-11/msg00922.php
http://archives.postgresql.org/pgsql-general/2007-11/msg00931.php

Likely my problem is that I don't use a function to do the cast.

That forces me to manually have to recreate the cast
each time a new database is set up and usually that's the step that
gets forgotten.

Surely you have a script that creates your databases for you?

Is this enough script?:

DUMP:
/usr/bin/pg_dump -U [user] -Ft [dbname] > [tar_file]

RESTORE:
/usr/bin/pg_restore -c -Ft [tar_file] | /usr/bin/psql -U [user] [dbname]

Is there a function that will do what I want to convert the datatype
without having to create a CAST that PostgreSQL doesn't have natively?
How else are you supposed to use the ENCRYPT and DECRYPT functions?

With actual bytea types?

Sure, bytea works, but I want this to work:

SELECT DECRYPT(ENCRYPT('cheese', 'secret', 'bf'), 'secret', 'bf');

I don't see any BYTEA in there ...

Anyway this will convert for you - PG can get from an unknown quoted
literal to bytea just fine.
CREATE FUNCTION text2bytea(text) RETURNS bytea AS $_$
DECLARE
b bytea;
BEGIN
EXECUTE 'SELECT ' || quote_literal($1) || '::bytea' INTO b;
RETURN b;
END
$_$
LANGUAGE plpgsql;

Awesome! That's just what I was looking for!

And here's the cast definition that goes with it
CREATE CAST (text AS bytea) WITH FUNCTION public.text2bytea(text);

Perfect. And now that this CAST depends on a function which is in my
database, it should dump and restore without a problem.

Let me go test all this ... YEP THAT WORKS!

Thanks again!

-- Dante

D. Dante Lorenso wrote:

I know about creating a CAST from VARCHAR to BYTEA, but the problem
with a CAST is that it doesn't port to other database servers when I
do a dump and restore.

Doesn't it?
Hmm... seems to dump for me in 8.2

My CAST was defined as follows:

CREATE CAST (VARCHAR AS BYTEA) WITHOUT FUNCTION;

Tom explains why that does NOT dump and restore with my database here:

http://archives.postgresql.org/pgsql-general/2007-11/msg00922.php
http://archives.postgresql.org/pgsql-general/2007-11/msg00931.php

Likely my problem is that I don't use a function to do the cast.

Ah, it seems to be.

That forces me to manually have to recreate the cast
each time a new database is set up and usually that's the step that
gets forgotten.

Surely you have a script that creates your databases for you?

Is this enough script?:

DUMP:
/usr/bin/pg_dump -U [user] -Ft [dbname] > [tar_file]

RESTORE:
/usr/bin/pg_restore -c -Ft [tar_file] | /usr/bin/psql -U [user] [dbname]

Oh, you said new databases, by which I assumed you meant empty. Oh, if
you're dumping/restoring try -Fc - it's a lot more flexible if you want
to do partial restores etc.

Is there a function that will do what I want to convert the datatype
without having to create a CAST that PostgreSQL doesn't have
natively? How else are you supposed to use the ENCRYPT and DECRYPT
functions?

With actual bytea types?

Sure, bytea works, but I want this to work:

SELECT DECRYPT(ENCRYPT('cheese', 'secret', 'bf'), 'secret', 'bf');

I don't see any BYTEA in there ...

Well that's your problem - decrypt/encrypt operate on streams of bytes,
not characters. The reason being (presumably) that various
accents/symbols will have differing byte-codes in different encodings.

This means you'll need to be careful if you move between LATIN1 and
UTF-8 (for example) and you have passwords with odd characters.

Anyway this will convert for you

Perfect. And now that this CAST depends on a function which is in my
database, it should dump and restore without a problem.

Let me go test all this ... YEP THAT WORKS!

Great. If you find speed to be a problem you might want to look at
coding up a C function to do it. Shouldn't be difficult, since it
doesn't need to do anything to the data.

--
Richard Huxton
Archonet Ltd