Thread: Updating

Updating

From
Bob Pawley
Date:
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


Re: Updating

From
Adrian Klaver
Date:
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

Re: Updating

From
Andreas 'ads' Scherbaum
Date:
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

Re: Updating

From
Bob Pawley
Date:
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


Re: Updating

From
Adrian Klaver
Date:
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

Re: Updating

From
"Harvey, Allan AC"
Date:
> 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. 

Re: Updating

From
"Daniel Verite"
Date:
    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

Re: Updating

From
Adrian Klaver
Date:
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

Re: Updating

From
Bob Pawley
Date:
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


Re: Updating

From
"Daniel Verite"
Date:
    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

Re: Updating

From
Bob Pawley
Date:
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


Re: Updating

From
Tom Lane
Date:
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