Re: proposal: lob conversion functionality - Mailing list pgsql-hackers
From | Pavel Stehule |
---|---|
Subject | Re: proposal: lob conversion functionality |
Date | |
Msg-id | CAFj8pRDwEwuvknXu-LgB-xFNfxHViRbANDZUyyRtq0UH=-YkYQ@mail.gmail.com Whole thread Raw |
In response to | Re: proposal: lob conversion functionality (Pavel Stehule <pavel.stehule@gmail.com>) |
List | pgsql-hackers |
2013/8/25 Pavel Stehule <pavel.stehule@gmail.com>
it introduce a load_lo and make_lo functionsHellohere is a patch
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);
select xmlforest(encode(load_lo(24630), 'base64') AS attachment);
Regards
Pavel
RegardsPavel Stehule2013/8/22 Jov <amutu@amutu.com>+1badly 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.Jovblog: http:amutu.com/blog2013/8/10 Pavel Stehule <pavel.stehule@gmail.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
pgsql-hackers by date: