From: Nelson Gonzaga <ngonzaga@yahoo.com>
To: Greg Spiegelberg <gspiegelberg@gmail.com>
Cc: pgsql admin <pgsql-admin@postgresql.org>
Subject: Re: lo_import/lo_export in other table
Date: Tue, 16 Aug 2011 11:40:18 -0700 (PDT)
Message-ID: <1313520018.19282.YahooMailNeo@web161901.mail.bf1.yahoo.com> (raw)
In-Reply-To: <CAEtnbpVufqjTxGuhJMtoQ5uC3-k918yJ3jeccnBU0Z6=Aimz6A@mail.gmail.com>
References: <1313173875.46661.YahooMailNeo@web161913.mail.bf1.yahoo.com>
<CAEtnbpVufqjTxGuhJMtoQ5uC3-k918yJ3jeccnBU0Z6=Aimz6A@mail.gmail.com>
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@gmail.com>
Para: Nelson Gonzaga <ngonzaga@yahoo.com>
Cc: pgsql admin <pgsql-admin@postgresql.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@yahoo.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ngonzaga@yahoo.com, gspiegelberg@gmail.com
Subject: Re: lo_import/lo_export in other table
In-Reply-To: <1313520018.19282.YahooMailNeo@web161901.mail.bf1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox