Re: proposal: lob conversion functionality

Hello

2013/8/12 Pavel Stehule <pavel.stehule@gmail.com>:
> 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.
>>

so simplified functionality should have a little bit different names
than original LO API:

/** saving bytea to lo (with possibility enter a target loid)*/
FUNCTION make_lo(src bytea, loid oid DEFAULT -1) RETURNS oid

/** loading from lo to bytea
*/
FUNCTION load_lo(loid) RETURNS bytea

This API is simple and friendly to PL languages, and for more complex
and specific work, there is still older LO server side API

Regards

Pavel

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