badly need the large object and bytea convert function.
Once I have to use the ugly pg_read_file() to put some text to pg,I tried to use large object but find it is useless without function to convert large object to bytea.
I had to enhance my older project, where XML documents are parsed and created on server side - in PLpgSQL and PLPerl procedures. We would to use a LO API for client server communication, but we have to parse/serialize LO on server side.
I found so there are no simple API for working with LO from PL without access to file system. I had to use a ugly hacks:
CREATE OR REPLACE FUNCTION parser.save_as_lob(bytea) RETURNS oid AS $$ DECLARE _loid oid; _substr bytea; BEGIN _loid := lo_creat(-1); FOR i IN 0..length($1)/2048 LOOP _substr := substring($1 FROM i * 2048 + 1 FOR 2048); IF _substr <> '' THEN INSERT INTO pg_largeobject(loid, pageno, data) VALUES(_loid, i, _substr); END IF; END LOOP;
EXECUTE format('GRANT SELECT ON LARGE OBJECT %s TO ohs', _loid); RETURN _loid; END; $$ LANGUAGE plpgsql SECURITY DEFINER STRICT SET search_path = 'pg_catalog';
and
CREATE OR REPLACE FUNCTION fbuilder.attachment_to_xml(attachment oid) RETURNS xml AS $$ DECLARE b_cum bytea = ''; b bytea; BEGIN FOR b IN SELECT l.data FROM pg_largeobject l WHERE l.loid = attachment_to_xml.attachment ORDER BY l.pageno LOOP b_cum := b_cum || b; END LOOP; IF NOT FOUND THEN RETURN NULL; ELSE RETURN xmlelement(NAME "attachment", encode(b_cum, 'base64')); END IF; END; $$ LANGUAGE plpgsql STRICT SECURITY DEFINER SET search_path = 'pg_catalog';
These functions can be simplified if we supports some functions like encode, decode for LO
So my proposal is creating functions:
* lo_encode(loid oid) .. returns bytea * lo_encode(loid oid, encoding text) .. returns text * lo_make(loid oid, data bytea) * lo_make(loid oid, data text, encoding text)
This can simplify all transformation between LO and VARLENA. Known limit is 1G for varlena, but it is still relative enough high.