Re: Retrieve the record ID

[Shane Ambler <pgsql(at)Sheeky(dot)Biz>]

Luca Ciciriello wrote:
> Hi all.
> I'm new to this list and, first of all, I'm a new user of PostgreSQL.
> The version I'm using is 8.2.3 and I've the necessity to retrieve, using an
> application, the ID of a modified (INSERT, UPDATE, DELETE) record of a
> triggered table. I wasn't able to find out a way to obtain the required ID.
> The only information available outside of the DBMS and usable by my app
> (subscribed for a significant event) are the table name and the server
> process id.
The last insert is the easy one - SELECT currval('mtable_id_seq');

To get the ID of an UPDATE or DELETE you will need a trigger to be run 
on each event. If the trigger you refer to is your own then I would 
integrate the change into what you have, if you are referring to 
cascading updates/deletes then you will need to add a trigger to suit 
your needs.

Within the trigger you have access to 'NEW' and 'OLD' copies of the row 
affected, which is where you can get the ID you are after.
(see chapter 37.10)

The information passed with a notify is simply an event name so you may 
want a table to store the ID's you want. Maybe with a timestamp that you 
app uses to find changes since it last looked. Polling this table can be 
an alternate to using notify.

> The application is running on Windows XP and the server is installed on
> Windows Server 2003.
> Any one knows a way to notify my app with the ID of the modified record?
> Any idea is appreciated.
>
> Thanks in advance.
>
> Luca
>
> _________________________________________________________________
> Watch all 9 Live Earth concerts live on MSN.  http://liveearth.uk.msn.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq


--

Shane Ambler
pgSQL(at)Sheeky(dot)Biz

Get Sheeky @ http://Sheeky.Biz