From: | Féliciano Matias <feliciano(dot)matias(at)free(dot)fr> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: psql and bytea |
Date: | 2003-05-15 10:29:21 |
Message-ID: | 1052994560.18364.6.camel@one.myworld |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Sorry for my poor English.
First, postgresql is a beautiful DBMS !
I try to use bytea type with psql to store some files (i know, there are
\lo_*).
There are some informations in the man page of psql :
------------------------------------
Another possible use of this mechanism is to copy the contents of a file
into a field. First load the file into a variable and then proceed as
above.
testdb=> \set content ’\’’ ‘cat my_file.txt‘ ’\’’
testdb=> INSERT INTO my_table VALUES (:content);
One possible problem with this approach is that my_file.txt might
contain single quotes. These need to be escaped so that they don’t cause
a syntax error when the third line is processed. This could be done
with the program sed:
testdb=> \set content ’\’’ ‘sed -e "s/’/\\\\\\’/g" < my_file.txt‘ ’\’’
But this does not work with binary string (bytea) since i want to store
any kind of files.
------------------------------------
I check the documentation (really fantastic documentation ! Great job):
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=datatype-binary.html
I build two very simple tools to convert a binary stream to a bytea
stream.
------------------------------------
tobytea_sql.c :
/*
* convert a stream to bytea.
* The '\' is escaped, so it can be used in a SQL statement.
*/
#include <stdio.h>
int main(void) {
int c ;
while ((c = getchar()) != EOF) {
if ( c == '\'' || c == '\\' ||
c < 32 || c > 126 ) {
printf("\\\\%d%d%d",
(c % (8*8*8)) / (8*8),
(c % (8*8)) / 8,
c % 8) ;
}
else {
putchar(c) ;
}
}
return 0 ;
}
------------------------------------
/*
* convert a bytea stream
* This is not compatible with the output of tobytea_sql !
* tobytea_sql put two \ . frombytea espect only one !
*/
#include <stdio.h>
int main(void) {
int c ;
int car ;
while ((c = getchar()) != EOF) {
if ( c < 32 || c > 126) {
fprintf(stderr, "Char ignored\n") ;
break ;
}
if ( c == '\\' ) {
if ((c = getchar()) == EOF) return 0 ;
car = (c - '0') * 8 * 8 ;
if ((c = getchar()) == EOF) return 0 ;
car += (c - '0') * 8 ;
if ((c = getchar()) == EOF) return 0 ;
car += c - '0' ;
putchar(car) ;
}
else {
putchar(c) ;
}
}
return 0 ;
}
------------------------------------
How to use it ?
insert into a table :
$ psql
[...]
one=> create temp table tmp (data bytea) ;
CREATE TABLE
one=> -- push some data in a psql variable
one=> \set content '\''`tobytea_sql < data`'\''
one=> \echo :content
'o\\030\\251\\273C7\\266K\\331 ... [snip] ... \323W\\027\\256D'
one=> -- insert
one=> insert into tmp (data) values (:content) ;
INSERT 35042356 1
one=> -- vrite the content of temp.data to a file
one=> \a
Output format is unaligned.
one=> \t
Showing only tuples.
one=> \o | frombytea > data
one=> select data from tmp ;
one=> Char ignored -- psql send a CR at the end of the row
(frombytea drop it since CR = 10 and it is not a "normal" char for a
bytea stream).
Is there a bug in psql ? Check this :
WARNING : this example take a lot of memory !
Create a big file with some random data :
$ dd if=/dev/urandom bs=1M count=20 of=data
$ #the bytea stream is about 74 Mo !)
$ tobytea < data | wc
0 81574 74379444
$ psql
[...]
one=> \set content '\''`tobytea_sql < data`'\''
one=> insert into tmp (data) values(:content) ;
one'> -- Problem !
one'> ' -- try to continue
one(> ) ;
INSERT 35042373 1
one=> select length(data) from tmp ;
length
--
18924736
(1 row)
one=> -- this is not the original size (20M : 20971520).
I don't have any problems with smaller files (10Mo is always fine).
All of this is used with postgresql 7.3.2 shipped with Red Hat Linux 9 .
--
Féliciano Matias <feliciano(dot)matias(at)free(dot)fr>
From | Date | Subject | |
---|---|---|---|
Next Message | Kingsley Idehen | 2003-05-15 11:22:04 | Re: ODBC drivers for osX |
Previous Message | Ewald Geschwinde | 2003-05-15 10:26:13 | Re: problem |