Re: proposal: lob conversion functionality

2013/8/25 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>

> Hello
>
> here is a patch
>
> it introduce a load_lo and make_lo functions
>
> postgres=# select make_lo(decode('ffffff00','hex'));
> make_lo
> ─────────
> 24629
> (1 row)
>
> Time: 40.724 ms
> postgres=# select load_lo(24628);
> load_lo
> ────────────
> \xffffff00
> (1 row)
>
> postgres=# \lo_import ~/avatar.png
> lo_import 24630
>
> postgres=# select md5(load_lo(24630));
> md5
> ──────────────────────────────────
> 513f60836f3b625713acaf1c19b6ea78
> (1 row)
>
> postgres=# \q
> bash-4.1$ md5sum ~/avatar.png
> 513f60836f3b625713acaf1c19b6ea78 /home/pavel/avatar.png
>
>
my motivation and reason for implementation of this patch was little bit
difficult implementation of conversion blob to base64 string.

I have a communication system based on XML documents. These documents are
created in PG with XML functions. There was customer request to support
binary (images) attachments. LO API is really very good for transport
binary data from client side to server side, but next processing was less
nice - LO -> Bytea transformation is not well documented and there is
necessary to use a magic integer constants. With these two functions this
transformations are very simple.

just

select xmlforest(encode(load_lo(24630), 'base64') AS attachment);

Regards

Pavel

> Regards
>
> Pavel Stehule
>
>
>
> 2013/8/22 Jov <amutu(at)amutu(dot)com>
>
>> +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
>>>
>>>
>>
>