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?

--

Kind Regards,
Keith