Re: proposal: lob conversion functionality

Hello

2013/8/12 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 2013/8/10 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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