Re: Use PSQLFS for photo storage

On Tue, Jan 13, 2009 at 06:22:34PM -0600, Jason Long wrote:
> Sam Mason wrote:
> >You'd need to generate the SQL somehow; if you know python it's probably
> >a pretty easy 20 or 30 lines of code to get this working.

> *Never used Python or Perl. I use primarily Java. I was thinking of
> doing something like
>
> *INSERT INTO pictures (filename,data) VALUES
> ('filename','/path/to/my/image/img0009.jpg');

If you're OK with using large objects, instead of "bytea"s, you can use
the lo_import function. You'd do something like:

CREATE TABLE pics (
path TEXT PRIMARY KEY,
data OID
);

INSERT INTO pics (path,data)
SELECT path, lo_import(path)
FROM (VALUES ('/path/to/my/image/img0009.jpg')) x(path);

This assumes that the files are accessable to the database server (i.e.
the paths are relative to the server daemon, not the psql command line
or whatever JDBC app you're driving this from).

Does that help any more?

Sam