in reply to PostgreSQL and binary data

Yes it can. I would avoid it like the plauge but, if you want to know.

Define your row. 
CREATE TABLE image {
    item_id    INTEGER NOT NULL,
    picture    OID,
    CONSTRAINT image_pk PRIMARY KEY(item_id),
    CONSTRAINT image_item_id_fk FOREIGN KEY(item_id) REFERENCES item(i
+tem_id)
};

[download]
Then do your inserts, selects and deletes 
INSERT INTO image VALUES (3, lo_import('/tmp/image_name.jpg'));
SELECT lo_export(picture, '/tmp/image_name.jpg') FROM image WHERE item
+_id = 3;
to delete:
SELECT lo_unlink(picture) FROM image WHERE item_id = 3;

[download]
WARNING: this code is untested, but double checked

FYI: why I suggest avoiding BLOBs.
Not portable to other RDBMSs
Filesystems are much better at handling files
Slows your DB down during BLOB access, usally only slightly
a general big pain in the A**

grep 
grep> cd pub 
grep> more beer

Replies are listed 'Best First'.
Re: Re: PostgreSQL and binary data
by rob_au (Abbot) on Dec 25, 2001 at 13:40 UTC
    I would have to strongly concur with grep's suggestion to avoid the storage of binary information, in this case, image files, in PostgreSQL. The amount of work involved in storing and extracting this data and the storage capacity it requires, far outweighs the indexing advantages offered by the database.

    By far a better solution is to storage all the image files in a known directory or directory structure and simply store a reference to this file in the database within a standard VARCHAR field. If the storing and indexing of the image files is an issue within itself, you may want to consider looking at Cache::Cache, in particular Cache::FileCache, and link the index of this cache to the database (if indeed you still require the database).

     

    perl -e 's&&rob@cowsnet.com.au&&&split/[@.]/&&s&.com.&_&&&print'

[reply]
[d/l]

In Section Seekers of Perl Wisdom