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');
|
|
References
|
References | ||
|---|---|---|