1 files changed, 79 insertions, 0 deletions
diff --git a/contrib/lo/lo_test.sql b/contrib/lo/lo_test.sql
new file mode 100644
index 0000000..7e52362
--- /dev/null
+++ b/contrib/lo/lo_test.sql
@@ -0,0 +1,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