Skip site navigation (1) Skip section navigation (2)

Re: Automatic export

From: Keith Worthington <KeithW(at)NarrowPathInc(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Cc: Michael Fuhr <mike(at)fuhr(dot)org>, "Daniel T(dot) Staal" <DStaal(at)usa(dot)net>
Subject: Re: Automatic export
Date: 2006-07-27 04:46:01
Message-ID: 44C84509.5020105@NarrowPathInc.com (view raw or flat)
Thread:
Lists: pgsql-novice
>>>I need to export several records of a single colum from a table in a
>>>database everytime that table is updated.  The data needs to end up in a
>>>text file. Can someone give me an idea on where to get started?  URL's to
>>>relevant documentation would be appreciated.  TIA
>>
>>I'd start here:
>>http://www.postgresql.org/docs/8.1/interactive/triggers.html
>>
>>Write a trigger on insert/update on that table, and have it export the
>>records.
> 
> Beware that triggers that perform actions outside the database won't
> have transactional semantics.  If you update a table and a trigger
> writes to an external file and then the transaction rolls back, the
> changes to the external file will remain.  If that could be a problem
> then consider using LISTEN/NOTIFY instead.  Notifications are sent
> only if a transaction commits, so you could have a rule or trigger
> that sends notifications and another process that listens for them
> and does whatever needs to be done.  A disadvantage is that this
> mechanism might require bookkeeping to know which rows to process.
> 
> http://www.postgresql.org/docs/8.1/interactive/sql-listen.html
> http://www.postgresql.org/docs/8.1/interactive/sql-notify.html
> http://www.postgresql.org/docs/8.1/interactive/libpq-notify.html

Hi All,

Daniel, Michael, Thank you very much for your answers.

My situation is such that if a change was made and then rolled back it 
would simply result in an unnecessary refresh of the output file.  That 
being said I am thinking that this is a good opportunity to expand on my 
limited knowledge of TRIGGERs and learn something completely new with 
LISTEN and NOTIFY.

So I read over the documentation and I am a bit unsure as to how this 
will work.  Here are my proposed code pieces so far.

-- Function: finance.tf_tbl_item_changed()
CREATE OR REPLACE FUNCTION finance.tf_tbl_item_changed()
   RETURNS "trigger" AS
$BODY$
    BEGIN
--    Send the notification signal.
       NOTIFY finance_tbl_item_changed;
    END;
$BODY$
   LANGUAGE 'plpgsql' VOLATILE;

-- Trigger: tgr_finance_tbl_item_changed on finance.tbl_item
CREATE TRIGGER tgr_finance_tbl_item_changed
   AFTER INSERT OR UPDATE OR DELETE
   ON finance.tbl_item
   FOR EACH STATEMENT
   EXECUTE PROCEDURE finance.tf_tbl_item_changed();

That might take care of the NOTIFY side of things.  Now in an attempt to 
handle the export side of things I propose this.

-- Function: interface.export_item_id()
CREATE OR REPLACE FUNCTION interface.export_item_id()
   RETURNS int4 AS
$BODY$
    BEGIN
--    Select the item_id into a temporary table.
       SELECT tbl_item.id AS item_id
         INTO TEMP tmp_item_id
         FROM finance.tbl_item
        WHERE ( NOT finance.tbl_item.inactive )
          AND ( finance.tbl_item.item_type = 'DIR' OR
                finance.tbl_item.item_type = 'NET' )
        ORDER BY item_id;
--    Export the data to a file.
       COPY tmp_item_id
         TO '/tmp/outfile.txt'
         WITH NULL AS '';
       RETURN 1;
    END;
$BODY$
   LANGUAGE 'plpgsql' VOLATILE;

Now here is where I am confused.
1)Where do I put the "LISTEN finance_tbl_item_changed;" command?
2)Do I need to restart the listen every time it runs?
3)How do I get the LISTEN command running?
4)How do I keep the LISTEN command running?

-- 

Kind Regards,
Keith

In response to

Responses

pgsql-novice by date

Next:From: Keith WorthingtonDate: 2006-07-27 04:55:29
Subject: Re: Lurking Wanna Be
Previous:From: Damian CDate: 2006-07-27 03:57:40
Subject: pg_dump : Mysterious"-b" switch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group