Re: lo_import/lo_export in other table

On Fri, Aug 12, 2011 at 12:31 PM, Nelson Gonzaga <ngonzaga(at)yahoo(dot)com> wrote:

> Hi all,
> How can I modify (or create other one) lo_import to save data in my table
> (not in pg_largeobject).
> Because I'm making an app that create a table with my fields and a bytea
> field, but .net doesn't read that field like lo_export.
>
> Any ideas?
>
> tks in advance,
>
>

I have a function that leverages the pg_largeobject table to load contents
into my own table. The current way, which works well enough for us right
now though it's under review, is a function that does this:

SELECT lo_import INTO my_oid FROM lo_import(path);
my_contents := '';
FOR block IN EXECUTE 'SELECT data FROM pg_largeobject WHERE loid=' ||
my_oid || ' ORDER BY pageno asc;'
LOOP
my_contents := my_contents || block;
END LOOP;
PERFORM lo_unlink(my_oid);
INSERT INTO public.contents (hash,bin_contents) VALUES
(my_hash,my_contents);

The hash is a text field in our table that today contains a MD5 hash. There
are checks in the function to ensure no two rows have the same hash to
prevent duplication. There's more to it that this but I want to say I've
seen it load 2,000+ files averaging about 24 KB in under 5 seconds.

There is a pg_readfile(text) function however the text path to the file must
be relative to the PGDATA directory. Didn't want our processes mucking
around in there so never gave it much more thought but it may serve your
purposes.

BTW, we went this route because INSERTs were taking far too long versus the
lo_import() and INSERTs also have all their values echo'd to the database
log file which slowed things down.

HTH,
Greg