Re: Automatic export - Mailing list 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