2013/8/10 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@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
RAISEEXCEPTION '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 $$
DECLAREfd integer;size integer;
BEGINfd := 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 referencedattachment_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
