Determine column name from trigger

Started by Envex Developmentsover 23 years ago2 messagesgeneral
Jump to latest
#1Envex Developments
mwagner@envex.net

Hey there,

I have a table named "program1", which looks like:

id INT NOT NULL
u1 INT
u2 INT
u3 INT
ds INT

I'm trying to keep a consitent summary of this table through the use of a
trigger, and function written in PL/pgSQL. The problem I'm having is when
the trigger is executed AFTER an UPDATE statement, there's no way to
determine which uX column was updated. I need to know if it's u1, u2, or
u3.

I've been trying for loops such as:

-------------------
FOR x IN 1 .. 3 LOOP
IF OLD.ux NOT = NEW.ux THEN
.... do this ...
END IF;
END LOOP;
-------------------

Obviously, that doesn't work, and I just receive errors stating the column
ux doesn't exist. Was wondering if anyone out there knew how to get the
column name which was updated through a trigger? Anyway to pass it as an
argument to the function or anything?

Any help would be greatly appreciated.

Thanks,
Matt

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Envex Developments (#1)
Re: Determine column name from trigger

"Envex Developments" <mwagner@envex.net> writes:

FOR x IN 1 .. 3 LOOP
IF OLD.ux NOT = NEW.ux THEN

I think you'll have to bite the bullet and write it out as three
separate tests:

if old.u1 <> new.u1 then
...
end if;
if old.u2 <> new.u2 then
...
end if;
if old.u3 <> new.u3 then
...
end if;

You could possibly do something that iterates through the column names
in pltcl, but plpgsql doesn't have that much flexibility.

regards, tom lane