2013/8/10 Tom Lane <t...@sss.pgh.pa.us>:
> Pavel Stehule <pavel.steh...@gmail.com> writes:
>> I found so there are no simple API for working with LO from PL without
>> access to file system.
>
> What? See lo_open(), loread(), lowrite(), etc.
>
yes, so there are three problems with these functions:
a) probably (I didn't find) undocumented
b) design with lo handler is little bit PL/pgSQL unfriendly.
CREATE OR REPLACE FUNCTION parser.save_as_lob(bytea)
RETURNS oid AS $$
DECLARE
loid oid;
fd integer;
bytes integer;
BEGIN
loid := lo_creat(-1);
fd := lo_open(loid, 131072);
bytes := lowrite(fd, $1);
IF (bytes != LENGTH($1)) THEN
RAISE EXCEPTION 'Not all data copied to blob';
END IF;
PERFORM lo_close(fd);
RETURN loid;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STRICT SET search_path = 'pg_catalog';
CREATE OR REPLACE FUNCTION fbuilder.attachment_to_bytea(attachment oid)
RETURNS bytea AS $$
DECLARE
fd integer;
size integer;
BEGIN
fd := lo_open(attachment, 262144);
size := lo_lseek(fd, 0, 2);
PERFORM lo_lseek(fd, 0, 0);
RETURN loread(fd, size);
EXCEPTION WHEN undefined_object THEN
PERFORM lo_close(fd);
RETURN NULL;
END;
$$ LANGUAGE plpgsql STRICT SECURITY DEFINER SET search_path = 'pg_catalog';
I had to use lot of magic constants, and getting size is not size too.
I believe so almost all reading will be a complete read, and then it
should be supported (maybe loread(fd, -1)).
c) probably there is a bug - it doesn't expect handling errors
postgres=# select fbuilder.attachment_to_xml(0);
WARNING: Snapshot reference leak: Snapshot 0x978f6f0 still referenced
attachment_to_xml
───────────────────
[null]
(1 row)
Time: 0.809 ms
>> These functions can be simplified if we supports some functions like
>> encode, decode for LO
>
> I do not see any good reason to tie encode/decode to LOs.
It can save a one transformations - but it is not too important and
can be easy done with current bytea API.
>
> regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
