Обсуждение: Using C functions with triggers

Hi:
   I have an application that uses libpq to communicate with a database.
From inside my application, I would like to define a 'trigger' and specify a
function that is in my application as the 'trigger func' parameter. (This is
all in C) Is this even possible? If not, how would one go about setting up
such a callback system? What I want is to be able to define triggers at
runtime from my application, and have different functions get executed when
the trigger fires. 
   (Being new to PostgreSQL, triggers might not even be what I need. I am
attempting to create a sort of 'node monitoring' system whereby my client
application can request to be notified whenever an insert, update or delete
happens to a row that is part of a view.)
   A concrete example would be: I have a table that contains a bunch of
contacts. I have a view setup to display all contacts that match a pattern.
I want to setup a trigger that tells me whenever my view is modified from
either an insert, update or delete. (If my trigger can't be isolated to a
view but only to a table, then I could live with that)
   Thank you for any help you can offer.

Sincerely,
Kenny Carruthers
pgmail@kennyc.com

Kenny Carruthers <pgmail@kennyc.com> writes:
>     I have an application that uses libpq to communicate with a database.
> From inside my application, I would like to define a 'trigger' and specify a
> function that is in my application as the 'trigger func' parameter. (This is
> all in C) Is this even possible?

You can't run application-side C code in the backend, no.

I think all you really need here are some rules to fire NOTIFY events:
ON INSERT TO mytable DO NOTIFY mytable_event;

and then listen for "mytable_event" notifications.
        regards, tom lane

On Mon, May 06, 2002 at 12:53:47AM -0700, Kenny Carruthers wrote:
> 
>     I have an application that uses libpq to communicate with a database.
> From inside my application, I would like to define a 'trigger' and specify a
> function that is in my application as the 'trigger func' parameter. (This is
> all in C) Is this even possible? If not, how would one go about setting up
> such a callback system? What I want is to be able to define triggers at
> runtime from my application, and have different functions get executed when
> the trigger fires. 
Only three worries there:

1. The trigger will not arrive while you're in the middle of a transaction.
(Not a bug, pretty smart choice when you think about it).  So if libpq
finds that your trigger gets notified but you've still got a transaction
going on that connection, it will defer delivery of the trigger until
after you've finished (committed or aborted) the transaction.

2. You'll need to poll for the trigger, ie. it won't arrive asynchronously
eg. while you're off executing some non-db-related loop somewhere.

3. If you sit still for long enough without doing anything, you might
lose your connection and have to reconnect at some inconvenient time.
Not that that can't happen otherwise, but for long-running programs it
might be an issue.

If you're into C++ you may want to look at the Trigger class in libpqxx,
my alternative C++ API to PostgreSQL, which hides this stuff behind a
simple callback interface.  (I followup to questions for free, but you 
get hit with advertisements in return ;-)   It's available from my home
page at http://members.ams.chello.nl/j.vermeulen31/proj-libpqxx.html


Jeroen