pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedlo_import/lo_export in other table
3+ messages / 2 participants
[nested] [flat]
* lo_import/lo_export in other table
@ 2011-08-12 18:31 Nelson Gonzaga <ngonzaga@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Nelson Gonzaga @ 2011-08-12 18:31 UTC (permalink / raw)
To: pgsql-admin
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,
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: lo_import/lo_export in other table
@ 2011-08-16 12:04 Greg Spiegelberg <gspiegelberg@gmail.com>
parent: Nelson Gonzaga <ngonzaga@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Greg Spiegelberg @ 2011-08-16 12:04 UTC (permalink / raw)
To: Nelson Gonzaga <ngonzaga@yahoo.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: lo_import/lo_export in other table
@ 2011-08-16 18:40 Nelson Gonzaga <ngonzaga@yahoo.com>
parent: Greg Spiegelberg <gspiegelberg@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Nelson Gonzaga @ 2011-08-16 18:40 UTC (permalink / raw)
To: Greg Spiegelberg <gspiegelberg@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-08-16 18:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-12 18:31 lo_import/lo_export in other table Nelson Gonzaga <ngonzaga@yahoo.com>
2011-08-16 12:04 ` Greg Spiegelberg <gspiegelberg@gmail.com>
2011-08-16 18:40 ` Nelson Gonzaga <ngonzaga@yahoo.com>
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