Hi,
I have been looking for a fairly simple way to upload data into
a bytea field without having to write custom C code for each table
that contains a bytea field.
With some good advice from Daniel Verite and reading the fine manual
here is my procedure for uploading files to bytea fields.
1) Create an "upload table" that might look like
\d pics Table "public.pics"
Column | Type | Modifiers
--------+---------
+---------------------------------------------------------
info | text |
image | bytea |
ident | integer | not null default
nextval('public.pics_ident_seq'::text)
2) I wrote a single C procedure that would upload to this table
here is a fragment of the code:
int usage()
{
fprintf(stderr,"loadBytea <connect string> <table> <comment>
<file>\n");
fprintf(stderr," This will insert the comment (a string) and the
contents of file\n");
fprintf(stderr," into the first two columns of the specified
table.\n");
exit(0);
}
The core of the program is a wrapper around the PQexecParams routine.
The source code for the program is located here:
http://homepage.mac.levanj/Cocoa/programs/loadBytea.c
3) Once the file is uploaded (say to table pics) I can use the update
command to load the bytea field into the desired table, perhaps
something like:
update person set picture = pics.image
from pics
where pics.ident=15 and person.first_name='Marijo'
Once the bytea field has been loaded into the target, it
can be deleted from the upload table ( or a reference could
be placed in the "person" table to the appropriate picture and
all of the pictures keep in the upload table).
This method does not scale well to bulk input but I think it
would not be difficult to rewrite the loadBytea.c program as
needed.
Jerry
