Re: lo_import/lo_export in other table

Greg,
Very cool your idea, I'll use it in my app.
And about lo_export, how can I get the file from this table?
 
Tks,
Nelson

De: Greg Spiegelberg <gspiegelberg(at)gmail(dot)com>
Para: Nelson Gonzaga <ngonzaga(at)yahoo(dot)com>
Cc: pgsql admin <pgsql-admin(at)postgresql(dot)org>
Enviadas: Terça-feira, 16 de Agosto de 2011 9:04
Assunto: Re: [ADMIN] 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