Re: [GENERAL] Function to convert from TEXT to BYTEA?
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Function to convert from TEXT to BYTEA?
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Function to convert from TEXT to BYTEA?
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 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? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Function to convert from TEXT to BYTEA?
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. 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. 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? -- Dante ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly