Re: Import file into bytea field in SQL/plpgsql?

On Mar 5, 10:20 am, d(dot)(dot)(dot)(at)archonet(dot)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