Обсуждение: Import file into bytea field in SQL/plpgsql?

Hi!

What I want to do:
Import a file from the file system into a bytea field of a table.

I know how to do it with large objects:
   INSERT INTO mytable(oid_fld) VALUES (lo_import('/mypath/myfile'));
And export from there:
   SELECT lo_export(oid_fld, '/mypath/myfile2') FROM mytable WHERE
<some condition>;

Now, I could copy over from pg_largeobject:
   INSERT INTO mytable(bytea_fld) SELECT data FROM pg_largeobject
WHERE loid = 1234567;
And create a large object and export from there as above.
But that seems unnecessarily complex, and  .. well .. stupid.

There must be a simpler way to import/export a file (as a whole, an
image for instance) into/out of my bytea field - in SQL or plpgsql?
Probably another set of functions I overlooked?


Thanks in advance
Erwin

Erwin Brandstetter wrote:
> Hi!
>
> What I want to do:
> Import a file from the file system into a bytea field of a table.

> Now, I could copy over from pg_largeobject:

> And create a large object and export from there as above.
> But that seems unnecessarily complex, and  .. well .. stupid.
>
> There must be a simpler way to import/export a file (as a whole, an
> image for instance) into/out of my bytea field - in SQL or plpgsql?
> Probably another set of functions I overlooked?

Not that I know of. It's simple enough to do from the application side
of things of course (well, in most languages) but there's no general
file access.

You can do various tricks to grab text values (see psql in the docs "SQL
Interpolation") but you'd need to escape the values. Not sure that's any
cleaner than the large-object approach.

--
   Richard Huxton
   Archonet Ltd

On Mar 5, 10:20 am, d...@archonet.com (Richard Huxton) wrote:
> Erwin Brandstetter wrote:
> > Hi!
>
> > What I want to do:
> > Import a file from the file system into a bytea field of a table.
(...)
> Not that I know of. It's simple enough to do from the application side
> of things of course (well, in most languages) but there's no general
> file access.
>
> You can do various tricks to grab text values (see psql in the docs "SQL
> Interpolation") but you'd need to escape the values. Not sure that's any
> cleaner than the large-object approach.


Thanks for the answer. "SQL Interpolation" is interesting (and
surprising) but not exactly clean, as you've implied. And I still
don't see a way to recreate a file from a bytea field other than with
lo_export.

I thought that maybe "COPY tbl(bytea_fld) FROM .. " /  "COPY
tbl(bytea_fld) TO .. " might do the trick, possibly with the BINARY
key word, but I didn't find a way.

The whole concept behind large objects is a bit off. Since we have
TOAST tables, it is of limited use to store large objects away in a
system table. It would be useful to have (additional) functions like:
   lo_import(text) RETURNS bytea
   lo_export(bytea, text) RETURNS integer

So we could import files into bytea fields with:
   INSERT INTO mytable (bytea_fld) VALUES(lo_import('/mypath/
myfile'));
and (re-)create one or more files with:
   SELECT lo_export(bytea_fld, filename_fld) FROM mytable WHERE <some
condition>;

That would probably be easy to implement for someone who knows the
large objects functions and C, i.e. someone who is not me.


Regards
Erwin

On 3/5/08, Erwin Brandstetter <brsaweda@gmail.com> wrote:
>  The whole concept behind large objects is a bit off. Since we have
>  TOAST tables, it is of limited use to store large objects away in a
>  system table. It would be useful to have (additional) functions like:
>    lo_import(text) RETURNS bytea
>    lo_export(bytea, text) RETURNS integer

Just so you know, large objects are quite old and predate TOAST, which
is why they are slightly quirky.  I agree that import/export functions
for BYTEA would be useful.


--
-Doug