PostgreSQL大对象类型

msn:jieshiyeskey@hotmail.com

1.定义方法：（用保留字OID定义大对象字段）

vjieshi=# create table jieshi(

vjieshi(# name char(20),

vjieshi(# photo OID);

CREATE TABLE

2.大对象引用

在PostgreSQL中，可以利用函数lo_import()将大对象插入到数据库中，利用lo_export()函数从数据库中取出大对象。

a.插入：

vjieshi=# insert into jieshi

vjieshi-# values ('markgeng',lo_import('/home/posgres/images/img_001.JPG'));

ERROR:  could not open server file "/home/posgres/images/img_001.JPG": No such file or directory

vjieshi=# insert into jieshi

values ('markgeng',lo_import('/home/postgres/images/img_001.JPG'));

INSERT 0 1

vjieshi=# insert into jieshi

values ('oracle',lo_import('/home/postgres/images/img_002.JPG'));

INSERT 0 1

vjieshi=# insert into jieshi

values ('耿彪',lo_import('/home/postgres/images/img_003.JPG'));

INSERT 0 1

vjieshi=# insert into jieshi

values ('节示',lo_import('/home/postgres/images/img_004.JPG'));

INSERT 0 1

vjieshi=# select * from jieshi;

          name          | photo

------------------------+-------

 markgeng               | 20769

 oracle                 | 20770

 耿彪                   | 20771

 节示                   | 20772

(4 rows)

b:倒出：

vjieshi=# select lo_export(jieshi.photo,'/home/postgres/outimages/img_001.jpg')

vjieshi-# from jieshi

vjieshi-# where name='markgeng';

 lo_export

-----------

         1

(1 row)

 

vjieshi=# select lo_export(jieshi.photo,'/home/postgres/outimages/img_002.jpg')

from jieshi

where photo='20770';

 lo_export

-----------

         1

(1 row)

[postgres@db-server-61-001 outimages]$ pwd

/home/postgres/outimages

[postgres@db-server-61-001 outimages]$ ls -l

total 3144

-rw-r--r-- 1 postgres postgres 1822911 Jun  5 21:48 img_001.jpg

-rw-r--r-- 1 postgres postgres 1391024 Jun  5 21:49 img_002.jpg

函数lo_unlink()可以删除大对象：

 

vjieshi=# select lo_unlink(jieshi.photo) from jieshi;

 lo_unlink

-----------

         1

         1

         1

         1

(4 rows)

vjieshi=# delete from jieshi;

DELETE 4

vjieshi=# select * from jieshi;

 name | photo

------+-------

(0 rows)