Re: proposal: lob conversion functionality

2013/8/10 Tom Lane :
> Pavel Stehule  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 $$
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