Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group