Re: Automatic export
От | Keith Worthington |
---|---|
Тема | Re: Automatic export |
Дата | |
Msg-id | 44C84509.5020105@NarrowPathInc.com обсуждение исходный текст |
Ответ на | Re: Automatic export (Michael Fuhr <mike@fuhr.org>) |
Ответы |
Re: Automatic export
|
Список | 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
В списке pgsql-novice по дате отправления: