>>>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:
>>Write a trigger on insert/update on that table, and have it export the
> 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.
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
-- Send the notification signal.
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
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
-- Select the item_id into a temporary table.
SELECT tbl_item.id AS item_id
INTO TEMP tmp_item_id
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.
WITH NULL AS '';
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?
In response to
pgsql-novice by date
|Next:||From: Keith Worthington||Date: 2006-07-27 04:55:29|
|Subject: Re: Lurking Wanna Be|
|Previous:||From: Damian C||Date: 2006-07-27 03:57:40|
|Subject: pg_dump : Mysterious"-b" switch|