Re: Automatic export
От | Keith Worthington |
---|---|
Тема | Re: Automatic export |
Дата | |
Msg-id | 44D8DCC9.8070105@NarrowPathInc.com обсуждение исходный текст |
Ответ на | Re: Automatic export (Keith Worthington <KeithW@NarrowPathInc.com>) |
Список | 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? > 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
В списке pgsql-novice по дате отправления:
Предыдущее
От: Keith WorthingtonДата:
Сообщение: Re: Scheduling data input from tab delimited file via php
Следующее
От: Steve CrawfordДата:
Сообщение: Re: Scheduling data input from tab delimited file via php