Re: Automatic export

>>>> 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