Re: proposal: lob conversion functionality

+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(dot)stehule(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>