Incredibly, I was just sitting down to do something similar for a
problem I have when I read this email. I'm going to do a temp table
too, but I did think of another solution. It would work for me but it's
a little complex for my stage of PG expertise: Create a user-defined
type for "pic" and define an input/output function for it. Someone can
correct me but my understanding says that probably won't be as fast as a
direct bulk copy, but it can't be slower than the temp table method and
it is certainly simpler.
Jerry LeVan wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
> joining column's datatypes do not match
>
>