Exporting a PDF from a bytea column

On 2/18/2016 4:44 PM, CS DBA wrote:

The system stores PDF's as large objects
in bytea columns.

Large Objects aka LO's and bytea columns are two completely different
things.

bytea columns are regular SQL database columns that contain a binary
byte array. large objects (LO) are a whole separate facility, where
each object is referenced by a unique OID, and the object is read and
written with the lo_XXX() functions in libpq, or equivalents in other
language specific bindings.
http://www.postgresql.org/docs/current/static/largeobjects.html

in a C program, an LO can be exported to a file via the API lo_export()
http://www.postgresql.org/docs/current/static/lo-interfaces.html#LO-EXPORT

Can anyone send me an example of
exporting from a bytea column to a PDF file?

I don't think you can get from a bytea field to a file without some
coding, as SQL scripting doesn't handle binary blobs very well.

--
john r pierce, recycling bits in santa cruz

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

On Thursday, February 18, 2016, John R Pierce <pierce@hogranch.com> wrote:

On 2/18/2016 4:44 PM, CS DBA wrote:

The system stores PDF's as large objects
in bytea columns.

Large Objects aka LO's and bytea columns are two completely different
things.

I'll assume the "column" is the most relevant term here because the above
is true.

Can anyone send me an example of

exporting from a bytea column to a PDF file?

I don't think you can get from a bytea field to a file without some
coding, as SQL scripting doesn't handle binary blobs very well.

/messages/by-id/AANLkTi=2dARHQPRGTXmCx0aus9maHQ2SfxxbvihtuzDv@mail.gmail.com

Short answer, to avoid the binary blob problem, is to encode the binary
data, export it, then decode it.

This can be done is psql. If your client can handle binary directly (e.g,
JDBC/Java) you can use that language's facilities to perform the
binary transfer directly thus bypassing the need to transcode.

David J,

On 02/18/2016 07:29 PM, David G. Johnston wrote:

On Thursday, February 18, 2016, John R Pierce <pierce@hogranch.com
<mailto:pierce@hogranch.com>> wrote:

On 2/18/2016 4:44 PM, CS DBA wrote:

The system stores PDF's as large objects
in bytea columns.

Large Objects aka LO's and bytea columns are two completely
different things.

I'll assume the "column" is the most relevant term here because the
above is true.

Can anyone send me an example of
exporting from a bytea column to a PDF file?

I don't think you can get from a bytea field to a file without
some coding, as SQL scripting doesn't handle binary blobs very well.

/messages/by-id/AANLkTi=2dARHQPRGTXmCx0aus9maHQ2SfxxbvihtuzDv@mail.gmail.com

Short answer, to avoid the binary blob problem, is to encode the
binary data, export it, then decode it.

This can be done is psql. If your client can handle binary directly
(e.g, JDBC/Java) you can use that language's facilities to perform the
binary transfer directly thus bypassing the need to transcode.

Can it be done from a Linux shell script? Any examples? Seems to be
little info on this in my googling?

On Thu, Feb 18, 2016 at 7:42 PM, CS DBA <cs_dba@consistentstate.com> wrote:

/messages/by-id/AANLkTi=2dARHQPRGTXmCx0aus9maHQ2SfxxbvihtuzDv@mail.gmail.com

Short answer, to avoid the binary blob problem, is to encode the binary
data, export it, then decode it.

This can be done is psql. If your client can handle binary directly (e.g,
JDBC/Java) you can use that language's facilities to perform the
binary transfer directly thus bypassing the need to transcode.

Can it be done from a Linux shell script? Any examples? Seems to be
little info on this in my googling?

​Others may provide actual examples but at the moment I don't have the time
to explore to that depth.

To answer your "shell script" question...anything can be done in a shell
script - as long as you have the right programs on your system to do the
actual work. I already mentioned "psql" which, if you want the least
amount of pure "coding", is going to be the helper program you will want to
use. And its usage is well documented. I would suggest base64 encoding
and then using whatever standard base64 decoder program exists on your
Linux box to perform the decoding.

David J.
​