Thread: Automatic export
Hi All, 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 Kind Regards, Keith
On Wed, July 26, 2006 3:17 pm, Keith Worthington said: > Hi All, > > 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. Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------
On Wed, Jul 26, 2006 at 03:27:01PM -0400, Daniel T. Staal wrote: > On Wed, July 26, 2006 3:17 pm, Keith Worthington said: > > 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 -- Michael Fuhr
>>>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
>>>> 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? > Hi All, Well, I have reread the documentation a couple of times and I still have not answered the questions I posed earlier. In addition I am wondering can I do this with a bash script? Does the bash script need to stay running via some sort of infinite loop and a sleep statement? Obviously I will need a cron entry to restart the script if it should fail or be killed. Finally how does PQNotifies play into this? TIA -- Kind Regards, Keith