From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com> |
Cc: | PGSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Function to convert from TEXT to BYTEA? |
Date: | 2007-12-12 18:45:05 |
Message-ID: | 47602C31.8050207@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2007-12-12 18:49:49 | Re: data type change on a view |
Previous Message | Scott Marlowe | 2007-12-12 18:43:27 | Re: General Q's |