Re: proposal: lob conversion functionality

On Thu, Sep 19, 2013 at 10:19 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:

> Hello
>
> here is patch
>

Looks good.

Marking it as Ready for Committer.

>
> Regards
>
> Pavel
>
>
>
> 2013/9/19 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>
>>
>>
>>
>> 2013/9/19 Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com>
>>
>>> Hi Pavel,
>>>
>>> I have reviewed you patch.
>>>
>>> -- Patch got applied cleanly (using patch -p1)
>>> -- Make & Make install works fine
>>> -- make check looks good
>>>
>>> I done code-walk and it looks good. Also did some manual testing and
>>> haven't
>>> found any issue with the implementation.
>>>
>>> Patch introduced two new API load_lo() and make_lo() for loading and
>>> saving
>>> from/to large objects Functions. When it comes to drop an lo object
>>> created
>>> using make_lo() this still depend on older API lo_unlink(). I think we
>>> should
>>> add that into documentation for the clerification.
>>>
>>> As a user to lo object function when I started testing this new API,
>>> first
>>> question came to mind is why delete_lo() or destroy_lo() API is missing.
>>> Later I realize that need to use lo_unlink() older API for that
>>> functionality.
>>> So I feel its good to document that. Do let you know what you think ?
>>>
>>
>> good idea
>>
>> I'll send a updated patch evening
>>
>>>
>>>
>>> Otherwise patch looks nice and clean.
>>>
>>>
>> Thank you :)
>>
>> Regards
>>
>> Pavel
>>
>>
>>> Regards,
>>> Rushabh Lathia
>>> www.EnterpriseDB.com
>>>
>>>
>>>
>>> On Sun, Aug 25, 2013 at 8:31 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:
>>>
>>>> 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
>>>>
>>>> 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
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> 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
>>>>
>>>>
>>>
>>>
>>> --
>>> Rushabh Lathia
>>>
>>
>>
>

--
Rushabh Lathia