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