contrib/lo/lo_test.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

/* contrib/lo/lo_test.sql */

-- Adjust this setting to control where the objects get created.
SET search_path = public;

--
-- This runs some common tests against the type
--
-- It's used just for development
--
-- XXX would be nice to turn this into a proper regression test
--

-- Check what is in pg_largeobject
SELECT count(oid) FROM pg_largeobject_metadata;

-- ignore any errors here - simply drop the table if it already exists
DROP TABLE a;

-- create the test table
CREATE TABLE a (fname name,image lo);

-- insert a null object
INSERT INTO a VALUES ('empty');

-- insert a large object based on a file
INSERT INTO a VALUES ('/etc/group', lo_import('/etc/group')::lo);

-- now select the table
SELECT * FROM a;

-- check that coercion to plain oid works
SELECT *,image::oid from a;

-- now test the trigger
CREATE TRIGGER t_a
BEFORE UPDATE OR DELETE ON a
FOR EACH ROW
EXECUTE PROCEDURE lo_manage(image);

-- insert
INSERT INTO a VALUES ('aa', lo_import('/etc/hosts'));
SELECT * FROM a
WHERE fname LIKE 'aa%';

-- update
UPDATE a SET image=lo_import('/etc/group')::lo
WHERE fname='aa';
SELECT * FROM a
WHERE fname LIKE 'aa%';

-- update the 'empty' row which should be null
UPDATE a SET image=lo_import('/etc/hosts')
WHERE fname='empty';
SELECT * FROM a
WHERE fname LIKE 'empty%';
UPDATE a SET image=null
WHERE fname='empty';
SELECT * FROM a
WHERE fname LIKE 'empty%';

-- delete the entry
DELETE FROM a
WHERE fname='aa';
SELECT * FROM a
WHERE fname LIKE 'aa%';

-- This deletes the table contents. Note, if you comment this out, and
-- expect the drop table to remove the objects, think again. The trigger
-- doesn't get fired by drop table.
DELETE FROM a;

-- finally drop the table
DROP TABLE a;

-- Check what is in pg_largeobject ... if different from original, trouble
SELECT count(oid) FROM pg_largeobject_metadata;

-- end of tests