Re: trigger output to a file - Mailing list pgsql-sql

I haven't given this a lot of thought, so take it with a grain of
salt. But my feeling is that publishing such a detailed log isn't the most
effective way to do this sort of thing. How about, instead, changing the
structure of your database to keep "old" information?  Consider, for
example, a simple phone book. You might have the following fields:

id  firstname   lastname   phone   fax

just to keep things simple.  How about, instead, having two tables:
1.) Records, which ONLY has the id column; and
2.) Data, which has:

id   rev   firstname   lastname   phone   fax

you can get what you're looking for by simply JOINing Records and
Data. Then, when you want to "change" a record - say, for example, Andrew
Perrin moves from Berkeley to Chapel Hill, thereby changing phones from
510-xxx-xxxx to 919-xxx-xxxx - you actually *add* a new record, with a
higher rev, to Data.  So, before:

id: 0
rev: 1
firstname: Andrew
lastname: Perrin
phone: 510-xxx-xxxx
fax:

And after:

id: 0
rev: 1
firstname: Andrew
lastname: Perrin
phone: 510-xxx-xxxx
fax:

id: 0
rev: 2
firstname: Andrew
lastname: Perrin
phone: 919-xxx-xxxx
fax:


SELECTing the highest rev will give you current data; selecting everything
for id 0 sorted by rev will give you the change history.

Just a thought.

Andy Perrin

----------------------------------------------------------------------
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology  
(Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
andrew_perrin@unc.edu - http://www.unc.edu/~aperrin

On Fri, 23 Mar 2001, Jan Wieck wrote:

> pgsql-sql wrote:
> > Hello Everyone,
> >
> > Here's my simple question.
> >
> > I just want to know/get the recent changes made to a table.
> > Deeper? I wanted the Postgresql server to *publish* every
> > changes made to a table (similar to replication, incremental transfer,
> > etc.).
> > What is the best way to go about it?
> >
> > My idea is to create a trigger for every insert/update that will
> > select the new/updated record and output it to a file. Or better
> > yet, I would like to output the actual sql query to file.
> > Is it possible to output the result of an sql query from a trigger
> > to a file? How?
> 
>     Putting the SQL query to a file would be my approach too.
> 
>     The  trigger approach lacks the capability to discard changes
>     already logged in case of a  transaction  rollback.  Thus,  I
>     wouldn't buy it.
> 
>     For the query string logging, alot more is required. Not only
>     the queries themself are  needed,  you'd  need  to  serialize
>     snapshot creation, log sequence allocations and the like. And
>     the program rolling forward this kind  of  log  into  another
>     database  needs  control mechanisms to tell the database that
>     it's in this recovery mode and has to ask for those values in
>     case it needs them.
> 
>     You might guess it already, I've been thinking about it for a
>     year or so now. And I'm still not past  the  point  to  start
>     implementing it.
> 
> >
> > I would appreciate any answer. Thank you very much.
> > Sherwin
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://www.postgresql.org/search.mpl
> >
> 
> 
> --
> 
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #================================================== JanWieck@Yahoo.com #
> 
> 
> 
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
>