Re: Function to convert from TEXT to BYTEA? - Mailing list pgsql-general

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