Re: psql and bytea

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.matias@free.fr>