PostgreSQL/Large Object

\dfS lo_*
-- lo_open(lobjId oid, mode integer) returns integer -- The mode parameter to lo_open uses two constants: -- INV_READ = 0x20000 -- INV_WRITE = 0x40000 -- INV_READ_WRITE = 0x60000 SELECT x'20000'::int; -- SELECT cast(x'20000' AS integer); -- 131072 SELECT x'40000'::int; -- SELECT cast(x'40000' AS integer); -- 262144 SELECT x'60000'::int; -- SELECT cast(x'60000' AS integer); -- 393216 SELECT (x'20000'|x'40000')::int -- SELECT cast(x'20000'|x'40000' AS integer); -- 393216
-- example: a SELECT lo_get(photo::oid), lo_get(resume::oid) FROM employee; -- select large object as binary data SELECT lo_from_bytea(0, 'Chorke Academia, Inc.'::bytea) FROM employee; -- create large object from ascii text SELECT lo_from_bytea(0, convert_to('Chorke Academia, Inc.', 'UTF8')) FROM employee; -- create large object from utf-8 text
-- example: b SELECT resume FROM profile; -- oid SELECT lo_get(resume::oid) FROM profile; -- binary SELECT encode(lo_get(resume::oid), 'escape') FROM profile; -- binary to text SELECT encode(lo_get(resume::oid), 'base64') FROM profile; -- binary to base64
-- example: c SELECT convert_from(lo_get(resume::oid), 'UTF8') FROM profile; SELECT convert_from(loread(lo_open(resume::oid, x'40000'::int), x'40000'::int), 'UTF8') FROM profile; SELECT lowrite(lo_open(lo_creat(-1), x'60000'::int), convert_to('Chorke Academia, Inc.', 'UTF8')) FROM profile; -- create large object SELECT lowrite(lo_open(resume::oid, x'60000'::int), convert_to('Chorke Academia, Inc.', 'UTF8')) FROM profile; -- update large object

Insert

Insert
-- right way to insert in insert INSERT INTO employee(id, resume, name) VALUES( (SELECT COALESCE(max(id), 0) + 1 FROM employee), lo_from_bytea(0, convert_to('Chorke Academia, Inc.', 'UTF8')), 'Chorke Academia, Inc.' );

Select

Select
-- right way to select in select SELECT id, name, resume AS resume_oid, lo_get(resume::oid) AS resume_binary, convert_from(lo_get(resume::oid), 'UTF8') AS resume_text FROM employee;

Update

Update
-- right way to create in update -- wrong way to update in update -- SELECT oid FROM pg_largeobject_metadata; UPDATE employee SET resume = lo_from_bytea(0, convert_to('Chorke Academia, Inc.', 'UTF8')) WHERE id = 1; -- SELECT oid FROM pg_largeobject_metadata; -- we should've to have 1 oid for a sigle record where it will -- be created N+1 oid for N times of update. after N times of -- update we need to delete N oid's from database else it will -- be wastage of huge disk space if those object not deleted! -- we need to delete manually and which one might be disaster -- SELECT lo_unlink(orphan_lo::oid);
-- right way to update in update -- SELECT oid FROM pg_largeobject_metadata; DO $BODY$ DECLARE p_id integer:= 1; p_oid integer:= 0; p_resume varchar:= 'Chorke Academia, Inc.'; BEGIN SELECT INTO p_oid COALESCE(m.oid, 0) FROM employee e LEFT JOIN pg_largeobject_metadata m ON e.resume::oid = m.oid WHERE e.id = p_id; IF p_oid > 0 THEN PERFORM lowrite(lo_open(p_oid::oid, x'60000'::int), convert_to(p_resume, 'UTF8')); ELSE UPDATE employee SET resume = lo_from_bytea(0, convert_to(p_resume, 'UTF8')) WHERE id = p_id; END IF; END $BODY$; -- SELECT oid FROM pg_largeobject_metadata; -- SELECT * FROM employee

Delete

Delete
-- wrong way to unlink in delete -- SELECT oid FROM pg_largeobject_metadata; DELETE FROM employee WHERE id = 1; -- SELECT oid FROM pg_largeobject_metadata; -- we should've to unlink respective oid's for those deleted -- records. Either it will be root causes of wasting of huge -- disk space if those object not deleted! we need to delete -- manually and which one might be disaster -- SELECT lo_unlink(orphan_lo::oid);
-- right way to unlink in delete -- SELECT oid FROM pg_largeobject_metadata; DO $BODY$ DECLARE p_id integer:= 1; p_oid integer:= 0; p_resume varchar:= 'Chorke Academia, Inc.'; BEGIN SELECT INTO p_oid COALESCE(m.oid, 0) FROM employee e LEFT JOIN pg_largeobject_metadata m ON e.resume::oid = m.oid WHERE e.id = p_id; DELETE FROM employee WHERE id = p_id; IF p_oid > 0 THEN PERFORM lo_unlink(p_oid::oid); END IF; END $BODY$; -- SELECT oid FROM pg_largeobject_metadata; -- SELECT * FROM employee;

Alter

Alter
ALTER TABLE employee -- ALTER COLUMN photo TYPE bytea USING lo_get(photo::oid); ALTER COLUMN photo TYPE text USING encode(lo_get(photo::oid), 'base64');