Re: proposal: lob conversion functionality

Hello

here is patch

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
>>
>
>