agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Steve Crawford <scrawford@pinpointresearch.com>
To: Adrian Chong <achong@cecid.hku.hk>
To: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Subject: Re: bytea
Date: Fri, 7 Feb 2003 10:54:32 -0800
Message-ID: <20030207185431.BB737103F7@polaris.pinpointresearch.com> (raw)
In-Reply-To: <016d01c2cd00$d7a2a5d0$20b10893@achongpc>
References: <3E40E45A.6D20C6EE@rodos.fzk.de>
<016d01c2cd00$d7a2a5d0$20b10893@achongpc>
That would be convenient but there are some difficulties. Say you have a
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
machine (or even the same type/os/filesystem) as the client so sending a
pathname to the server is of little use. Such an ability must be handled by
the client which can read/translate the local file into the appropriate byte
sequence to send to the server.
I'm toying with a client program in C program that would include the ability
to take a file on the client machine and store it in a bytea field but
haven't come up with a good way to handle the necessary buffer space (ie. I
don't see any way to "stream" data to the server - it appears I need to
allocate sufficient buffer space for the entire file plus the entire escaped
version of the file before I can generate the query which causes me some
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
view thread (17+ messages) latest in thread
Message-ID: <20030207185431.BB737103F7@polaris.pinpointresearch.com>
Permalink: ../20030207185431.BB737103F7@polaris.pinpointresearch.com/
Also on: postgresql.org/message-id/20030207185431.BB737103F7@polaris.pinpointresearch.com
reply
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-sql@postgresql.org
Cc: scrawford@pinpointresearch.com, achong@cecid.hku.hk, ch@rodos.fzk.de
Subject: Re: bytea
In-Reply-To: <20030207185431.BB737103F7@polaris.pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox