Re: Function to convert from TEXT to BYTEA?
От | D. Dante Lorenso |
---|---|
Тема | Re: Function to convert from TEXT to BYTEA? |
Дата | |
Msg-id | 47602691.4050103@lorenso.com обсуждение исходный текст |
Ответ на | Re: Function to convert from TEXT to BYTEA? (Richard Huxton <dev@archonet.com>) |
Ответы |
Re: Function to convert from TEXT to BYTEA?
(Richard Huxton <dev@archonet.com>)
|
Список | 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
В списке pgsql-general по дате отправления: