+1 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.
Jov blog: http:amutu.com/blog <http://amutu.com/blog> 2013/8/10 Pavel Stehule <pavel.steh...@gmail.com> > Hello > > 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. > > Notes. comments? > > Regards > > Pavel > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > >
