Simplfied Bytea input/output?

Hi,
I am adding image and large object support in my Cocoa
postgresql browser.

Are there going to be any enhanced bytea support functions
coming along?

It seems sorta silly to have to write customized C code
to import a file into a bytea field.

Maybe something like

CREATE TABLE image (
name text,
raster bytea
);

INSERT INTO image (name, raster)
VALUES ('beautiful image', bytea_import('/etc/motd'));

SELECT bytea_export(image.raster, '/tmp/motd') FROM image
WHERE name = 'beautiful image';

Jerry

On Wed, 4 Aug 2004, Jerry LeVan wrote:

> INSERT INTO image (name, raster)
> VALUES ('beautiful image', bytea_import('/etc/motd'));
>
> SELECT bytea_export(image.raster, '/tmp/motd') FROM image
> WHERE name = 'beautiful image';
>

Doing this kind of file/read write is only useful if the client and server
are on the same machine which is a kind of limiting restriction if you are
trying to make a generic data browsing tool. Further any function that
does file access like this must run with superuser permissions to prevent
misuse which is another serious restriction.

Kris Jurka

Thanks for the reply Kris...

I guess I did not state my case very clearly, what I would
like to see is a simple flexible tool that can get binary
data into the database...I can get it out fairly easy.

Of course it would be easy to "update" pg_hba.conf if you could
write files as postgres

Jerry

On Aug 4, 2004, at 3:40 PM, Kris Jurka wrote:

>
>
> On Wed, 4 Aug 2004, Jerry LeVan wrote:
>
>> INSERT INTO image (name, raster)
>> VALUES ('beautiful image', bytea_import('/etc/motd'));
>>
>> SELECT bytea_export(image.raster, '/tmp/motd') FROM image
>> WHERE name = 'beautiful image';
>>
>
> Doing this kind of file/read write is only useful if the client and
> server
> are on the same machine which is a kind of limiting restriction if you
> are
> trying to make a generic data browsing tool. Further any function that
> does file access like this must run with superuser permissions to
> prevent
> misuse which is another serious restriction.
>
> Kris Jurka
>

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

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
>
>