Thread: Updating
Is there a method available for triggering a function after an update on a particular column in a table? The only way that I have found is to trigger after an update on the whole table, which of course can lead to problems. Bob
On Sunday 16 March 2008 3:32 pm, Bob Pawley wrote: > Is there a method available for triggering a function after an update on a > particular column in a table? > > The only way that I have found is to trigger after an update on the whole > table, which of course can lead to problems. > > Bob I trigger can be constrained to fire for each row. Inside the trigger function you can test to see if the column in question has been updated and do the appropriate thing. If the column has not been changed do nothing and RETURN NEW which makes the function non-op. -- Adrian Klaver aklaver@comcast.net
Hello,
On Sun, 16 Mar 2008 15:32:27 -0700 Bob Pawley wrote:
> Is there a method available for triggering a function after an update on a
> particular column in a table?
>
> The only way that I have found is to trigger after an update on the whole
> table, which of course can lead to problems.
You can compare OLD.column and NEW.column and only continue your trigger
function, if you detect a change.
Kind regards
--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Would it be possible to get an example of such coding?? Bob ----- Original Message ----- From: "Adrian Klaver" <aklaver@comcast.net> To: <pgsql-general@postgresql.org> Cc: "Bob Pawley" <rjpawley@shaw.ca> Sent: Sunday, March 16, 2008 5:14 PM Subject: Re: [GENERAL] Updating > On Sunday 16 March 2008 3:32 pm, Bob Pawley wrote: >> Is there a method available for triggering a function after an update on >> a >> particular column in a table? >> >> The only way that I have found is to trigger after an update on the whole >> table, which of course can lead to problems. >> >> Bob > > I trigger can be constrained to fire for each row. Inside the trigger > function > you can test to see if the column in question has been updated and do the > appropriate thing. If the column has not been changed do nothing and > RETURN > NEW which makes the function non-op. > -- > Adrian Klaver > aklaver@comcast.net > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On Sunday 16 March 2008 5:36 pm, Bob Pawley wrote: > Would it be possible to get an example of such coding?? > > Bob > > > ----- Original Message ----- > From: "Adrian Klaver" <aklaver@comcast.net> > To: <pgsql-general@postgresql.org> > Cc: "Bob Pawley" <rjpawley@shaw.ca> > Sent: Sunday, March 16, 2008 5:14 PM > Subject: Re: [GENERAL] Updating > > > On Sunday 16 March 2008 3:32 pm, Bob Pawley wrote: > >> Is there a method available for triggering a function after an update on > >> a > >> particular column in a table? > >> > >> The only way that I have found is to trigger after an update on the > >> whole table, which of course can lead to problems. > >> > >> Bob > > > > I trigger can be constrained to fire for each row. Inside the trigger > > function > > you can test to see if the column in question has been updated and do the > > appropriate thing. If the column has not been changed do nothing and > > RETURN > > NEW which makes the function non-op. > > -- > > Adrian Klaver > > aklaver@comcast.net > > > > -- > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general CREATE FUNCTION foo() RETURNS trigger AS $Body$ BEGIN IF NEW.colname != OLD.colname THEN ..."Do something"..; RETURN whatever; ELSE RETURN NEW: END IF; END; $Body$ LANGUAGE plpgsql; CREATE TRIGGER foo_test BEFORE UPDATE ON foo_table FOR EACH ROW EXECUTE PROCEDURE foo(); -- Adrian Klaver aklaver@comcast.net
> Would it be possible to get an example of such coding??
This trigger has an argument passed. When the trigger is "assigned"
I know whether the column is of type txt or float.
It uses the column name to determine what to do.
Hope this helps Allan
create or replace function insert_if_diff() returns trigger as
$BODY$
declare
r record;
begin
for r in execute 'select ' || TG_ARGV[0] || ' from ' || TG_TABLE_NAME || ' order by dt desc limit 1;'
loop
if TG_ARGV[0] = 'value'
then
if new.value = r.value
then
return null;
end if;
end if;
if TG_ARGV[0] = 'txt'
then
if new.txt = r.txt
then
return null;
end if;
end if;
return new;
end loop;
return NEW;
end;
$BODY$
language plpgsql;
The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended
recipient,use, disclosure or copying of this information is prohibited. If you have received this document in error,
pleaseadvise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses
containedin this email or any attachments.
Adrian Klaver wrote: > CREATE FUNCTION foo() RETURNS trigger AS > $Body$ > BEGIN > IF NEW.colname != OLD.colname THEN > ..."Do something"..; > RETURN whatever; > ELSE > RETURN NEW: > END IF; > END; > $Body$ LANGUAGE plpgsql; Beware that the "Do something" code path will not be taken when the column goes from NULL to non-NULL or non-NULL to NULL. In the general case where the column is nullable, better use "IS DISTINCT FROM" instead of inequality: IF NEW.colname IS DISTINCT FROM OLD.colname Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
On Monday 17 March 2008 4:54 am, Daniel Verite wrote: > Adrian Klaver wrote: > > CREATE FUNCTION foo() RETURNS trigger AS > > $Body$ > > BEGIN > > IF NEW.colname != OLD.colname THEN > > ..."Do something"..; > > RETURN whatever; > > ELSE > > RETURN NEW: > > END IF; > > END; > > $Body$ LANGUAGE plpgsql; > > Beware that the "Do something" code path will not be taken when the > column goes from NULL to non-NULL or non-NULL to NULL. > > In the general case where the column is nullable, better use "IS > DISTINCT FROM" instead of inequality: > IF NEW.colname IS DISTINCT FROM OLD.colname > > Best regards, > -- > Daniel > PostgreSQL-powered mail user agent and storage: > http://www.manitou-mail.org Thanks for the heads up. This is a case I usually only remember when I start testing the function. -- Adrian Klaver aklaver@comcast.net
I am attempting to use the following code but I get - "ERROR: NEW used in query that is not in a rule". This implies that I create a rule for NEW and OLD (which I haven't needed before). Could someone point me to the proper synatx for such a rule? BTW I noticed that Adrian used != . Is this symbol the same as <> ? Bob ----- Original Message ----- From: "Adrian Klaver" <aklaver@comcast.net> To: <pgsql-general@postgresql.org> Cc: "Daniel Verite" <daniel@manitou-mail.org>; "Bob Pawley" <rjpawley@shaw.ca> Sent: Monday, March 17, 2008 7:16 AM Subject: Re: [GENERAL] Updating > On Monday 17 March 2008 4:54 am, Daniel Verite wrote: >> Adrian Klaver wrote: >> > CREATE FUNCTION foo() RETURNS trigger AS >> > $Body$ >> > BEGIN >> > IF NEW.colname != OLD.colname THEN >> > ..."Do something"..; >> > RETURN whatever; >> > ELSE >> > RETURN NEW: >> > END IF; >> > END; >> > $Body$ LANGUAGE plpgsql; >> >> Beware that the "Do something" code path will not be taken when the >> column goes from NULL to non-NULL or non-NULL to NULL. >> >> In the general case where the column is nullable, better use "IS >> DISTINCT FROM" instead of inequality: >> IF NEW.colname IS DISTINCT FROM OLD.colname >> >> Best regards, >> -- >> Daniel >> PostgreSQL-powered mail user agent and storage: >> http://www.manitou-mail.org > > Thanks for the heads up. This is a case I usually only remember when I > start > testing the function. > -- > Adrian Klaver > aklaver@comcast.net > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Bob Pawley wrote: > I am attempting to use the following code but I get - > "ERROR: NEW used in query that is not in a rule". > > This implies that I create a rule for NEW and OLD (which I haven't needed > before). No, but are you sure you're using these keywords in the context of a plpgsql function? Can you post the entire CREATE statement that fails? > BTW I noticed that Adrian used != . Is this symbol the same as <> ? Yes it's the same. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
Following is the code that gives me the error. CREATE OR REPLACE FUNCTION monitor_install() RETURNS trigger AS $$ Begin If NEW.p_id.association.monitoring_fluid is distinct from Old.p_id.association.monitoring_fluid Then INSERT INTO p_id.devices (device_number) (Select mon_function from p_id.association, p_id.devices Where (p_id.association.mon_function <> p_id.devices.device_number and (p_id.association.monitoring_fluid <> p_id.devices.fluid_id or p_id.association.monitoring_fluid <> p_id.devices.pipe_id)) and p_id.association.monitor is null); RETURN NULL; END; $$ LANGUAGE plpgsql; create trigger monitorinstall before update on p_id.association for each row execute procedure monitor_install(); ----- Original Message ----- From: "Daniel Verite" <daniel@manitou-mail.org> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Adrian Klaver" <aklaver@comcast.net>; <pgsql-general@postgresql.org> Sent: Monday, March 17, 2008 2:42 PM Subject: Re: [GENERAL] Updating > Bob Pawley wrote: > >> I am attempting to use the following code but I get - >> "ERROR: NEW used in query that is not in a rule". >> >> This implies that I create a rule for NEW and OLD (which I haven't > needed >> before). > > No, but are you sure you're using these keywords in the context of a > plpgsql function? > Can you post the entire CREATE statement that fails? > >> BTW I noticed that Adrian used != . Is this symbol the same as <> ? > > Yes it's the same. > > Best regards, > -- > Daniel > PostgreSQL-powered mail user agent and storage: > http://www.manitou-mail.org > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Bob Pawley <rjpawley@shaw.ca> writes:
> If NEW.p_id.association.monitoring_fluid is distinct from
> Old.p_id.association.monitoring_fluid Then
Surely this should just be
if new.monitoring_fluid is distinct from old.monitoring_fluid then
Also, I think you forgot an "end if" and a "return new" at the end.
regards, tom lane