Re: proposal: lob conversion functionality

Hello

here is patch

Regards

Pavel

2013/9/19 Pavel Stehule <pavel.stehule@gmail.com>

2013/9/19 Rushabh Lathia <rushabh.lathia@gmail.com>

Hi Pavel,

I have reviewed you patch.

-- Patch got applied cleanly (using patch -p1)

-- Make & Make install works fine

-- make check looks good

I done code-walk and it looks good. Also did some manual testing and haven't

found any issue with the implementation.

Patch introduced two new API load_lo() and make_lo() for loading and saving

from/to large objects Functions. When it comes to drop an lo object created

using make_lo() this still depend on older API lo_unlink(). I think we should

add that into documentation for the clerification. 

As a user to lo object function when I started testing this new API, first

question came to mind is why delete_lo() or destroy_lo() API is missing.

Later I realize that need to use lo_unlink() older API for that functionality.

So I feel its good to document that. Do let you know what you think ?

good idea

I'll send a updated patch evening

 

Otherwise patch looks nice and clean.

Thank you :)

Regards

Pavel

 

Regards,

Rushabh Lathia

www.EnterpriseDB.com

On Sun, Aug 25, 2013 at 8:31 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hello

here is a patch

it introduce a load_lo and make_lo functions

postgres=# select make_lo(decode('ffffff00','hex'));
 make_lo
─────────
   24629
(1 row)

Time: 40.724 ms
postgres=# select load_lo(24628);
  load_lo  
────────────
 \xffffff00
(1 row)

postgres=# \lo_import ~/avatar.png
lo_import 24630

postgres=# select md5(load_lo(24630));
               md5               
──────────────────────────────────
 513f60836f3b625713acaf1c19b6ea78
(1 row)

postgres=# \q
bash-4.1$ md5sum ~/avatar.png
513f60836f3b625713acaf1c19b6ea78  /home/pavel/avatar.png

Regards

Pavel Stehule

2013/8/22 Jov <amutu@amutu.com>

+1

badly need the large object and bytea convert function.

  

Once I have to use the ugly pg_read_file() to put some text to pg,I tried to use large object but find it is useless without function to convert large object to bytea. 

Jov

blog: http:amutu.com/blog

2013/8/10 Pavel Stehule <pavel.stehule@gmail.com>

Hello

I had to enhance my older project, where XML documents are parsed and
created on server side - in PLpgSQL and PLPerl procedures. We would to
use a LO API for client server communication, but we have to
parse/serialize LO on server side.

I found so there are no simple API for working with LO from PL without
access to file system. I had to use a ugly hacks:

CREATE OR REPLACE FUNCTION parser.save_as_lob(bytea)
RETURNS oid AS $$
DECLARE
  _loid oid;
  _substr bytea;
BEGIN
  _loid := lo_creat(-1);
  FOR i IN 0..length($1)/2048
  LOOP
    _substr := substring($1 FROM i * 2048 + 1 FOR 2048);
    IF _substr <> '' THEN
      INSERT INTO pg_largeobject(loid, pageno, data)
        VALUES(_loid, i, _substr);
    END IF;
  END LOOP;

  EXECUTE format('GRANT SELECT ON LARGE OBJECT %s TO ohs', _loid);
  RETURN _loid;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STRICT SET search_path = 'pg_catalog';

and

CREATE OR REPLACE FUNCTION fbuilder.attachment_to_xml(attachment oid)
RETURNS xml AS $$
DECLARE
  b_cum bytea = '';
  b bytea;
BEGIN
  FOR b IN SELECT l.data
              FROM pg_largeobject l
             WHERE l.loid = attachment_to_xml.attachment
             ORDER BY l.pageno
  LOOP
    b_cum := b_cum || b;
  END LOOP;
  IF NOT FOUND THEN
    RETURN NULL;
  ELSE
    RETURN xmlelement(NAME "attachment",
                                       encode(b_cum, 'base64'));
  END IF;
END;
$$ LANGUAGE plpgsql STRICT SECURITY DEFINER SET search_path = 'pg_catalog';

These functions can be simplified if we supports some functions like
encode, decode for LO

So my proposal is creating functions:

* lo_encode(loid oid) .. returns bytea
* lo_encode(loid oid, encoding text) .. returns text
* lo_make(loid oid, data bytea)
* lo_make(loid oid, data text, encoding text)

This can simplify all transformation between LO and VARLENA. Known
limit is 1G for varlena, but it is still relative enough high.

Notes. comments?

Regards

Pavel


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
Rushabh Lathia