pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBytea question
6+ messages / 3 participants
[nested] [flat]
* Bytea question
@ 2007-04-11 13:02 Chris Hoover <revoohc@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Chris Hoover @ 2007-04-11 13:02 UTC (permalink / raw)
To: pgsql-admin
Hopefully a quick question.
If I store a file into a bytea field, is there anyway for postgres to read
the contents of the file?
Here is my scenario:
Receive text file
insert file into bytea field
insert trigger fires
- read file contents
- post contents into production tables
Is this at all possible, or am I going to need to store the original file
and store/pass the file contents?
Thanks,
Chris
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bytea question
@ 2007-04-11 13:09 Alvaro Herrera <alvherre@commandprompt.com>
parent: Chris Hoover <revoohc@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Alvaro Herrera @ 2007-04-11 13:09 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
Chris Hoover escribió:
> Hopefully a quick question.
>
> If I store a file into a bytea field, is there anyway for postgres to read
> the contents of the file?
When you say "store a file", do you mean "store a filename"?
> Here is my scenario:
>
> Receive text file
> insert file into bytea field
> insert trigger fires
> - read file contents
> - post contents into production tables
>
> Is this at all possible, or am I going to need to store the original file
> and store/pass the file contents?
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bytea question
@ 2007-04-11 13:17 Chris Hoover <revoohc@gmail.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 2 replies; 6+ messages in thread
From: Chris Hoover @ 2007-04-11 13:17 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; pgsql-admin
No, I need to store the entire file as received into the database.
Chris
On 4/11/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:
>
> Chris Hoover escribió:
> > Hopefully a quick question.
> >
> > If I store a file into a bytea field, is there anyway for postgres to
> read
> > the contents of the file?
>
> When you say "store a file", do you mean "store a filename"?
>
> > Here is my scenario:
> >
> > Receive text file
> > insert file into bytea field
> > insert trigger fires
> > - read file contents
> > - post contents into production tables
> >
> > Is this at all possible, or am I going to need to store the original
> file
> > and store/pass the file contents?
>
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bytea question
@ 2007-04-11 13:22 Alvaro Herrera <alvherre@commandprompt.com>
parent: Chris Hoover <revoohc@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Alvaro Herrera @ 2007-04-11 13:22 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
Chris Hoover escribió:
> No, I need to store the entire file as received into the database.
Then you original question does not make any sense to me, because if you
pass the entire file content, why would Postgres *not* be able to read
it?
> On 4/11/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> >
> >Chris Hoover escribió:
> >> Hopefully a quick question.
> >>
> >> If I store a file into a bytea field, is there anyway for postgres to
> >read
> >> the contents of the file?
> >
> >When you say "store a file", do you mean "store a filename"?
> >
> >> Here is my scenario:
> >>
> >> Receive text file
> >> insert file into bytea field
> >> insert trigger fires
> >> - read file contents
> >> - post contents into production tables
> >>
> >> Is this at all possible, or am I going to need to store the original
> >file
> >> and store/pass the file contents?
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bytea question
@ 2007-04-11 13:32 Chris Hoover <revoohc@gmail.com>
parent: Chris Hoover <revoohc@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Chris Hoover @ 2007-04-11 13:32 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; pgsql-admin
What I am needing to do is to store a file I receive, say file1.dat. I want
to store file1.dat into a bytea field so we can always go back and retrieve
the original file to verify if needed. However, I also need to process the
data that is in file1.dat (1234 johnny 1254.12). When I load file1.dat into
the bytea field, I just have a large escaped record that represents the
file. What I am hoping is possible is to have postgres look into file1.dat's
byte a field and be able to read the data in file1.dat (assuming it's a
simple text file). That way, the database can do all of the heavy lifting
and loading.
so.
Insert file1.dat into a bytea field of table a.
Table a's insert trigger fires, and postgres looks at the bytea field and
see's file1.dat and its contents of "1234 johnny 1254.12".
Trigger runs logic on data and loads into appropriate tables
I hope this makes sense.
Chris
On 4/11/07, Chris Hoover <revoohc@gmail.com> wrote:
>
> No, I need to store the entire file as received into the database.
>
> Chris
>
> On 4/11/07, Alvaro Herrera < alvherre@commandprompt.com> wrote:
> >
> > Chris Hoover escribió:
> > > Hopefully a quick question.
> > >
> > > If I store a file into a bytea field, is there anyway for postgres to
> > read
> > > the contents of the file?
> >
> > When you say "store a file", do you mean "store a filename"?
> >
> > > Here is my scenario:
> > >
> > > Receive text file
> > > insert file into bytea field
> > > insert trigger fires
> > > - read file contents
> > > - post contents into production tables
> > >
> > > Is this at all possible, or am I going to need to store the original
> > file
> > > and store/pass the file contents?
> >
> >
> > --
> > Alvaro Herrera
> > http://www.CommandPrompt.com/
> > PostgreSQL Replication, Consulting, Custom Development, 24x7 support
> >
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bytea question
@ 2007-04-11 13:40 Mike Goldner <mgoldner@agmednet.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 6+ messages in thread
From: Mike Goldner @ 2007-04-11 13:40 UTC (permalink / raw)
To: pgsql-admin; +Cc: Chris Hoover <revoohc@gmail.com>
It sounds like you want to parse the contents of the bytea column within
a trigger/stored procedure. I haven't done this myself (I process the
bytea data in java as an input stream after retrieving it from
postgres), but you could probably use a FOR loop over the bytea column
and then use some combination of the Binary String Functions
(http://www.postgresql.org/docs/current/static/functions-binarystring.html) to process the file data.
Mike
On Wed, 2007-04-11 at 09:22 -0400, Alvaro Herrera wrote:
> Chris Hoover escribió:
> > No, I need to store the entire file as received into the database.
>
> Then you original question does not make any sense to me, because if you
> pass the entire file content, why would Postgres *not* be able to read
> it?
>
>
> > On 4/11/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> > >
> > >Chris Hoover escribió:
> > >> Hopefully a quick question.
> > >>
> > >> If I store a file into a bytea field, is there anyway for postgres to
> > >read
> > >> the contents of the file?
> > >
> > >When you say "store a file", do you mean "store a filename"?
> > >
> > >> Here is my scenario:
> > >>
> > >> Receive text file
> > >> insert file into bytea field
> > >> insert trigger fires
> > >> - read file contents
> > >> - post contents into production tables
> > >>
> > >> Is this at all possible, or am I going to need to store the original
> > >file
> > >> and store/pass the file contents?
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-04-11 13:40 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-11 13:02 Bytea question Chris Hoover <revoohc@gmail.com>
2007-04-11 13:09 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-04-11 13:17 ` Chris Hoover <revoohc@gmail.com>
2007-04-11 13:22 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-04-11 13:40 ` Mike Goldner <mgoldner@agmednet.com>
2007-04-11 13:32 ` Chris Hoover <revoohc@gmail.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