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

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