Content-Type: text/plain;
  charset="iso-8859-2"
From: Steve Crawford <scrawford@pinpointresearch.com>
Organization: Pinpoint Research
To: "Adrian Chong" <achong@cecid.hku.hk>,
	"Christoph Haller" <ch@rodos.fzk.de>, <pgsql-sql@postgresql.org>
Subject: Re: bytea
Date: Fri, 7 Feb 2003 10:54:32 -0800
References: <3E40E45A.6D20C6EE@rodos.fzk.de>
	<016d01c2cd00$d7a2a5d0$20b10893@achongpc>
In-Reply-To: <016d01c2cd00$d7a2a5d0$20b10893@achongpc>
MIME-Version: 1.0
Message-Id: <20030207185431.BB737103F7@polaris.pinpointresearch.com>
Content-Transfer-Encoding: quoted-printable

That would be convenient but there are some difficulties. Say you have a=20
function that worked something like:
insert.....values (...., filetobytea("/home/me/myfile"),...);
It would be a nice ability to have but the server may not be on the same=20
machine (or even the same type/os/filesystem) as the client so sending a=20
pathname to the server is of little use. Such an ability must be handled by=
=20
the client which can read/translate the local file into the appropriate byt=
e=20
sequence to send to the server.

I'm toying with a client program in C program that would include the abilit=
y=20
to take a file on the client machine and store it in a bytea field but=20
haven't come up with a good way to handle the necessary buffer space (ie. I=
=20
don't see any way to "stream" data to the server - it appears I need to=20
allocate sufficient buffer space for the entire file plus the entire escape=
d=20
version of the file before I can generate the query which causes me some=20
other problems.) Any suggestions would be appreciated.

Cheers,
Steve


On Wednesday 05 February 2003 2:25 am, Adrian Chong wrote:
> Hi Christoph,
>
> Thanks for your reply. But what I want to do is loading a file of a
> particular path with a sql statement in psql. Why I need to care about how
> the file looks like? Thanks.
>
> Adrian
>
> ----- Original Message -----
> From: "Christoph Haller" <ch@rodos.fzk.de>
> To: <pgsql-sql@postgresql.org>
> Cc: <achong@cecid.hku.hk>
> Sent: Wednesday, February 05, 2003 6:15 PM
> Subject: Re: [SQL] bytea
>
> > > I have a table containing a field of type bytea:
> > >
> > > CREATE TABLE a_table (
> > >     a_field bytea
> > > );
> > >
> > > How can I import a file in a SQL script? What function I can use?
> >
> > The documentation says as in PostgreSQL 7.2.1 (I doubt this changed
> > significantly since)
> >
> > Octets of certain values must be escaped (but all octet values may be
> > escaped) when used as part of a string literal in an SQL
> > statement. In general, to escape an octet, it is converted into the
> > three-digit octal number equivalent of its decimal octet value, and
> > preceded by two backslashes.
> >
> > In general it goes like this
> > INSERT INTO a_table ( a_field ) VALUES ( '\\000\\001\\002\\003' ) ;
> > to load the first four ASCII characters.
> > You did not mention how your file looks like.
> > There is also a C function available called PQescapeBytea
> > which does all the required escaping to store memory areas in bytea
> > columns.
> > Refer to Command Execution Functions within libpq - C Library for
> > details.
> >
> > Regards, Christoph
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster