12.1 Working with PostgreSQL BLOBs Using SQL

The first topic in this chapter is PostgreSQL's SQL interface to BLOBs. PostgreSQL provides a set of easy-to-use functions for working with BLOBs. Using these functions is not difficult because most of these functions are only SQL counterparts of widely used C functions.

First of all, a BLOB is not stored in a table but as an object in the database. A BLOB must be accessed via a so-called object id, which is a unique identifier of an object in a database. The object id of a BLOB can then be stored in a table so that it does not get lost in the database.

Let's create a table for storing object ids and a description of the files the object id is pointing to:

 phpbook=# CREATE TABLE ext4(file_oid oid, description text); CREATE 

The first column of the table contains the object id of the file you want to reference. The second column contains the description of the file.

When creating a new table containing object ids, you should keep in mind that a column should not be called oid:

 phpbook=# CREATE TABLE ext5(oid oid, description text); ERROR:  name of column "oid" conflicts with an existing system column ERROR:  name of column "oid" conflicts with an existing system column 

Internally, PostgreSQL uses a column for storing the object id of a row in a table. If an additional column is called oid, there will be a conflict as you saw in the preceding listing. To retrieve the object id from the table, you must add oid to the list of columns you want to display.

 phpbook=# SELECT oid, * FROM ext4;   oid  | file_oid |         description -------+----------+------------------------------  44294 |    44293 | file used to store user data  44296 |    44295 | hostnames 

Since PostgreSQL 7.2, it is possible to create tables without having a column containing an object id:

 phpbook=# CREATE TABLE ext5(oid oid, description text) WITHOUT OIDS; CREATE 

Just add WITHOUT OIDS to the CREATE TABLE statement, and there will be no conflicts if the names of the columns are duplicated:

 phpbook=# \ d ext5           Table "ext5"    Column    | Type | Modifiers -------------+------+-----------  oid         | oid  |  description | text | 

As you can see, the table has been created successfully.

Let's get back to the first example you saw in this section. To import a file into the database, you can use lo_import:

 phpbook=# SELECT lo_import('/etc/passwd');  lo_import -----------      44293 (1 row) 

As you can see, the function returns an object id the file has been stored in the database but it has not been imported into a table.

To insert the object id returned by lo_import into the table, you can use an INSERT statement.

 phpbook=# INSERT INTO ext4 VALUES (44293, 'file used to store user data'); INSERT 44294 1 

However, using two commands is not the best way to perform this kind of operation. To import a file and add a record to the table, you can also write a SQL statement as shown in the next listing:

 phpbook=# INSERT INTO ext4 VALUES (lo_import('/etc/hosts'), 'hostnames'); INSERT 44296 1 

To see if the record has been generated successfully, you can query the table:

 phpbook=# SELECT * FROM ext4;  file_oid |         description ----------+------------------------------     44293 | file used to store user data     44295 | hostnames (2 rows) 

Two records have been added to the table, and that's the way you want it to be.

Now that you have seen how to import data, it's time to see how to export BLOBs. To export a BLOB, PostgreSQL provides a command called lo_export:

 phpbook=# SELECT lo_export(44293, '/tmp/tmpfile.txt');  lo_export -----------          1 (1 row) 

The first parameter passed to the function is the object id of the file. The second parameter defines the filename you want to export the BLOB to. Keep in mind that after a file has been imported into a PostgreSQL database, the original filename is lost PostgreSQL does not need it because the new identification in the database is just an object id. When exporting the file again, you must assign a name to the file.

Let's take a look at the content of the new file. With the help of head -n3, the first three lines of the file can be displayed:

 [hs@athlon hs]$ head /tmp/tmpfile.txt -n3 root:x:0:0:root:/root:/bin/bash bin:x:1:1:bin:/bin: daemon:x:2:2:daemon:/sbin: 

The file has been exported successfully.

The next example shows how one object including the entry of the object id in ext4 can be removed:

 phpbook=# BEGIN; BEGIN phpbook=# SELECT lo_unlink(44295);  lo_unlink -----------          1 (1 row) phpbook=# DELETE FROM ext4 WHERE file_oid=44295; DELETE 1 phpbook=# COMMIT; COMMIT 

We recommend performing the operation in a single transaction so that concurrent operations do not affect your work on the database. lo_unlink removes a BLOB keep in mind that this does not mean that the object id is automatically removed from ext4 because this must be done by a separate DELETE statement.

In addition to the functions you have just seen, PostgreSQL provides additional functions for handling BLOBs, such as lo_open and lo_write. However, in most cases you will not use these functions as SQL commands because PHP provides an easy-to-use interface for handling BLOBs as well.