Обсуждение: problem urgent - please help

Hi,

I have just registered to the postgresql jdbc lidt and wanted to post a
problem of mine which is very urgent to solve, so please help me out.

I have written a Java application that reads the data from a postgresql
database and retrieves a particular field, performs some actions on
that field and finally store the calculated data in some other
table.The postgresql database, however is changing continuously , since
new data is being added every minute. My problem is that I want to
write some trigger in my java application that would be invoked as soon
as there is a change in the database and perform the above actions for
that newly added row.

Is this even possible using a Java application, if yes please guide me
how to do this, if no , what can be another way to read the data
dynamically from a database through a Java application.

Please help me as I am in midst of a project and have been digging into
this for quite long without any productive results.

Regards,
Vishal Bhatia

vbhatia@ksu.edu wrote:
> I have written a Java application that reads the data from a postgresql
> database and retrieves a particular field, performs some actions on
> that field and finally store the calculated data in some other
> table.The postgresql database, however is changing continuously , since
> new data is being added every minute. My problem is that I want to
> write some trigger in my java application that would be invoked as soon
> as there is a change in the database and perform the above actions for
> that newly added row.

You can write a trigger in Java.  If you're asking if a trigger in
PostgreSQL can invoke your standalone Java program, the short answer is
no.  You can probably rig something up using an untrusted PL language,
though.  There is no untrusted Java implementation, though, so you'd
have to use another one to implement the glue code.  Sounds like your
best bet would be to write a trigger in Java, and just run the whole
thing in the DB.

--
Guy Rouillier

Guy Rouillier wrote:
> vbhatia@ksu.edu wrote:
>> I have written a Java application that reads the data from a postgresql
>> database and retrieves a particular field, performs some actions on
>> that field and finally store the calculated data in some other
>> table.The postgresql database, however is changing continuously , since
>> new data is being added every minute. My problem is that I want to
>> write some trigger in my java application that would be invoked as soon
>> as there is a change in the database and perform the above actions for
>> that newly added row.
>
> You can write a trigger in Java.  If you're asking if a trigger in
> PostgreSQL can invoke your standalone Java program, the short answer is
> no.  You can probably rig something up using an untrusted PL language,
> though.  There is no untrusted Java implementation, though, so you'd
> have to use another one to implement the glue code.  Sounds like your
> best bet would be to write a trigger in Java, and just run the whole
> thing in the DB.

Another option would be to use LISTEN/NOTIFY. You would have a Java thread
LISTENING on events, and use triggers on tables (e.g. using PL/pgSQL) to
NOTIFY the thread on updates. Than the Java code can do what ever is needed.

See the documentation (Server):
http://www.postgresql.org/docs/8.2/static/sql-listen.html
http://www.postgresql.org/docs/8.2/static/libpq-notify.html
And for the JDBC interface:
http://jdbc.postgresql.org/documentation/82/listennotify.html

Perhaps that helps.

Best Regards
Michael Paesold