Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 222569FB2F9 for ; Wed, 11 Apr 2007 10:32:39 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 25913-03 for ; Wed, 11 Apr 2007 10:32:27 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.243]) by postgresql.org (Postfix) with ESMTP id EED579FB2DC for ; Wed, 11 Apr 2007 10:32:32 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c24so195871ana for ; Wed, 11 Apr 2007 06:32:31 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=mTpAdAJanJ5MXrPUsqNK+Gj8HsEf///hJ0IaBkzzh4cNu5dFJbeDRlUF+Z1h5KFhIVHYkV+ZY0vuOHbuGmC+8Gcl1c2UH1nlv9s3eUw94OeXxmzSINynuBkkr/DM9bIncCJ+uhx9owoEsY69U27f8HP2XjiYNklaF/I0M3JP/VY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=AMmBN9F1gI2Km27Wr3tUMQZRFhlrFtHObQEumzxzK405HuILQu5/Ss7qh8xVyQOOCcBvP4yEP6SXvB7RKpDVXi+iIp7KfTemP7T2HySUuyGBO5u6rOHgPp6gjaAMMVGWZOldSCUGwFqVvv82MiSToT0jFPd1XbTYmz6Gj76ObEA= Received: by 10.100.7.18 with SMTP id 18mr397891ang.1176298351770; Wed, 11 Apr 2007 06:32:31 -0700 (PDT) Received: by 10.100.165.19 with HTTP; Wed, 11 Apr 2007 06:32:31 -0700 (PDT) Message-ID: <1d219a6f0704110632k7448f319i7cecbb7899b1d959@mail.gmail.com> Date: Wed, 11 Apr 2007 09:32:31 -0400 From: "Chris Hoover" To: "Chris Hoover" , "pgsql-admin@postgresql.org" Subject: Re: Bytea question In-Reply-To: <1d219a6f0704110617y31780a75q8f783893acb6ef07@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6638_17969645.1176298351328" References: <1d219a6f0704110602n5b9fe58dy34e05008b9bff0be@mail.gmail.com> <20070411130904.GD4896@alvh.no-ip.org> <1d219a6f0704110617y31780a75q8f783893acb6ef07@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/98 X-Sequence-Number: 25162 ------=_Part_6638_17969645.1176298351328 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline What I am needing to do is to store a file I receive, say file1.dat. I wan= t 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 int= o 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 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=F3: > > > 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 > > > > ------=_Part_6638_17969645.1176298351328 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 r= etrieve the original file to verify if needed.  However, I also need t= o process the data that is in=20 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=20 file1.dat (assuming it's a simple text file).  That way, the datab= ase can do all of the heavy lifting and loading.

so.

Insert f= ile1.dat into a bytea field of table a.
Table a's insert trigger fir= es, and postgres looks at the bytea field and see's=20 file1.dat and its contents of "1234 johnny 1254.12". 
Tr= igger runs logic on data and loads into appropriate tables

I hope th= is makes sense.

Chris


On= 4/11/07,=20 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=F3:
> 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 &quo= t;store a file", do you mean "store a filename"?

> Here is my scenario:
>
> Receive text file
> insert = file into bytea field
> insert trigger fires
>  - rea= d 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&= nbsp;           &nbs= p;            &= nbsp;      http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 suppor= t


------=_Part_6638_17969645.1176298351328--