Re: proposal: lob conversion functionality

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