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

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

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, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments.

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

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

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 <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