Re: create function and trigger to update column on table update

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Dara Olson" <dolson(at)glifwc(dot)org>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: create function and trigger to update column on table update
Date: 2010-12-16 15:12:54
Message-ID: 21114.1292512374@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

"Dara Olson" <dolson(at)glifwc(dot)org> writes:
> I am new to creating triggers/functions. I am trying to create a trigger and function that when a specific table is updated or records added that it updates an existing column (catalog_number) from an existing column and latitude/longitude. Below is what I have so far. Does anyone know what I am doing wrong or if I am going in the complete wrong direction? Is there a way to define that the column only gets updated on the records that were updated or inserted. Any help would be greatly appreciated!

Yeah, you're going in the wrong direction. You don't want to issue a
new UPDATE from an UPDATE trigger: that will just send you into an
infinite loop of repeated updates. What you want to do is (1) use a
BEFORE trigger, not an AFTER trigger, and (2) modify the NEW row, rather
than looking at the table proper. So the body of the function would look
something like

NEW.catalog_number := "tsn_char" || x(centroid(transform(NEW.the_geom, 4326))) || y(centroid(transform(NEW.the_geom, 4326)));
RETURN NEW;

if I've guessed at the intent of your code correctly. There are
examples to look at in the plpgsql chapter of the manual.

regards, tom lane

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Dara Olson 2010-12-16 16:58:20 Re: create function and trigger to update column on table update
Previous Message Josh Kupershmidt 2010-12-16 02:51:39 Re: create function and trigger to update column on table update