Thread: Creating Functions & Triggers

Creating Functions & Triggers

From

Kevin Lohka

Date:

20 March 2004, 21:34:17

Hello everyone, I'm new to creating functions & triggers on postgresql
and am having trouble creating a trigger to update a record with the
modification date and the current user.

My code is below, but doesn't work.  It hangs psql when I attempt to
modify the record.  The function and trigger are created successfully.

CREATE FUNCTION email_mod_date() RETURNS OPAQUE AS '
   BEGIN
         UPDATE email SET  m_date = current_date, m_by_user =
current_user
         WHERE id = NEW.id;

   RETURN NULL;
   END;
'LANGUAGE 'plpgsql';



CREATE TRIGGER email_mod_date
AFTER UPDATE
ON email
FOR EACH ROW
EXECUTE PROCEDURE email_mod_date();

Thanks for any help.

Kevin Lohka

Re: Creating Functions & Triggers

From

"V i s h a l Kashyap @ [Sai Hertz And Control Systems]"

Date:

21 March 2004, 02:11:06

Dear Kevin ,

> CREATE FUNCTION email_mod_date() RETURNS OPAQUE AS '
>   BEGIN
>         UPDATE email SET  m_date = current_date, m_by_user = current_user
>         WHERE id = NEW.id;
>
>   RETURN NULL;
>   END;
> 'LANGUAGE 'plpgsql';

CREATE FUNCTION email_mod_date() RETURNS OPAQUE AS '
 DECLARE
  BEGIN
        UPDATE email SET  m_date = current_date, m_by_user = current_user
        WHERE id = NEW.id;

  RETURN NEW;
  END;
'LANGUAGE 'plpgsql';

This must do the job.
But why you need a trigger for all this.

Just use a default value for the respective column's .


--
Best Regards,
Vishal Kashyap
Director / Lead Developer,
Sai Hertz And Control Systems Pvt Ltd,
http://saihertz.rediffblogs.com
Jabber IM: vishalkashyap@jabber.org
ICQ :      264360076
Yahoo  IM: mailforvishal@yahoo.com
-----------------------------------------------
You yourself, as much as anybody in the entire
universe, deserve your love and affection.
- Buddha
---------------
pgsql=# select marital_status from vishals_life;

marital_status
------------------
Single not looking

1 Row(s) affected

                    ___
                   //\\\
                  ( 0_0 )
----------------o0o-----o0o---------------------

Re: Creating Functions & Triggers

From

"V i s h a l Kashyap @ [Sai Hertz And Control Systems]"

Date:

21 March 2004, 02:53:04

Dear Kevin ,

>> CREATE FUNCTION email_mod_date() RETURNS TRIGGER AS '
>> DECLARE
>>  BEGIN
>>        UPDATE email SET  m_date = current_date, m_by_user = current_user
>>        WHERE id = NEW.id;
>>
>>  RETURN NEW;
>>  END;
>> 'LANGUAGE 'plpgsql';
>>
>> This must do the job.
>
>
> Thanks for the suggestion, unfortunately it still hangs when I try and
> update a record.
>
>> But why you need a trigger for all this.
>>
>> Just use a default value for the respective column's .
>
>
> Isn't the default value only used during the initial insert of a
> record?  How would I set the default value for an UPDATE only?

Yes, you can set the default value for update of a record
just with

UPDATE email SET  m_date = DEFAULT, m_by_user = DEFAULT  WHERE id = some_id;

I am doing this thing in  PostgreSQL 7.4.2

>
> Sorry for the basic questions, but I'm quite new to this.

Its ok, read above
Kindly pas on the query you are issuing .

--
Best Regards,
Vishal Kashyap
Director / Lead Developer,
Sai Hertz And Control Systems Pvt Ltd,
http://saihertz.rediffblogs.com
Jabber IM: vishalkashyap@jabber.org
ICQ :      264360076
Yahoo  IM: mailforvishal@yahoo.com
-----------------------------------------------
You yourself, as much as anybody in the entire
universe, deserve your love and affection.
- Buddha
---------------
pgsql=# select marital_status from vishals_life;

marital_status
------------------
Single not looking

1 Row(s) affected

                    ___
                   //\\\
                  ( 0_0 )
----------------o0o-----o0o---------------------

Re: Creating Functions & Triggers

From

Kevin Lohka

Date:

21 March 2004, 03:51:47

On Saturday, March 20, 2004, at 09:08 PM, V i s h a l Kashyap @ [Sai
Hertz And Control Systems] wrote:
>>
>>
>>> But why you need a trigger for all this.
>>>
>>> Just use a default value for the respective column's .
>>
>>
>> Isn't the default value only used during the initial insert of a
>> record?  How would I set the default value for an UPDATE only?
>
> Yes, you can set the default value for update of a record
> just with
>
> UPDATE email SET  m_date = DEFAULT, m_by_user = DEFAULT  WHERE id =
> some_id;

If I use this method, I will need to control the input from the client
side.  I was hoping to have an easy way to update the record with the
last date modified and user who modified the record regardless of where
the modification came from.  eg. Web or internal client software.
>
> I am doing this thing in  PostgreSQL 7.4.2

I'm using PostgreSQL 7.4.1 on Mac OS X 10.2.8
>
> Kindly pas on the query you are issuing .
>

acc=# UPDATE email SET email_address = 'mynewusername@mydomain.com'
WHERE id = 14;
-------------
UPDATE 1

Thank you for your time Vishal.

Kevin Lohka

Re: Creating Functions & Triggers

From

"V i s h a l Kashyap @ [Sai Hertz And Control Systems]"

Date:

21 March 2004, 04:03:42

Dear Kevin  ,

>>
>> Yes, you can set the default value for update of a record
>> just with
>>
>> UPDATE email SET  m_date = DEFAULT, m_by_user = DEFAULT  WHERE id =
>> some_id;
>
>
> If I use this method, I will need to control the input from the client
> side.  I was hoping to have an easy way to update the record with the
> last date modified and user who modified the record regardless of
> where the modification came from.  eg. Web or internal client software.

If the below query is issed by the web client then your job could be
done by the said above query as well .
To my limited knowledge  no need to write a trigger.
Just try using this

CREATE FUNCTION email_mod_date() RETURNS TRIGGER AS

            ^^^^^^^^

>>
>
> acc=# UPDATE email SET email_address = 'mynewusername@mydomain.com'
> WHERE id = 14;
> -------------
> UPDATE 1

I suppose this is update has been done without the trigger in place.

To my limited knowledge is the trigger going into a sort of race condition

>
> Thank you for your time Vishal.


Oh!,
Its my pleaseure.

But kindly  pass on the solution if you have found one.

--
Best Regards,
Vishal Kashyap
Director / Lead Developer,
Sai Hertz And Control Systems Pvt Ltd,
http://saihertz.rediffblogs.com
Jabber IM: vishalkashyap@jabber.org
ICQ :      264360076
Yahoo  IM: mailforvishal@yahoo.com
-----------------------------------------------
You yourself, as much as anybody in the entire
universe, deserve your love and affection.
- Buddha
---------------
pgsql=# select marital_status from vishals_life;

marital_status
------------------
Single not looking

1 Row(s) affected

                    ___
                   //\\\
                  ( 0_0 )
----------------o0o-----o0o---------------------

Re: Creating Functions & Triggers

From

Kevin Lohka

Date:

21 March 2004, 04:18:30

On Saturday, March 20, 2004, at 10:23 PM, V i s h a l Kashyap @ [Sai
Hertz And Control Systems] wrote:

> Dear Kevin  ,
>
>>>
>>> Yes, you can set the default value for update of a record
>>> just with
>>>
>>> UPDATE email SET  m_date = DEFAULT, m_by_user = DEFAULT  WHERE id =
>>> some_id;
>>
>>
>> If I use this method, I will need to control the input from the
>> client side.  I was hoping to have an easy way to update the record
>> with the last date modified and user who modified the record
>> regardless of where the modification came from.  eg. Web or internal
>> client software.
>
> If the below query is issed by the web client then your job could be
> done by the said above query as well .
> To my limited knowledge  no need to write a trigger.
> Just try using this
>
> CREATE FUNCTION email_mod_date() RETURNS TRIGGER AS
>
>             ^^^^^^^^

I'll give it a try.

>>>
>>
>> acc=# UPDATE email SET email_address = 'mynewusername@mydomain.com'
>> WHERE id = 14;
>> -------------
>> UPDATE 1
>
> I suppose this is update has been done without the trigger in place.

That's correct, when the function & trigger are in place psql just
hangs.  It does not accept any input and the only way I know how to
terminate the process is to restart postmaster from another process.

>
> But kindly  pass on the solution if you have found one.
>
>
As soon as I find one I will. I'm sure that I just have a simple syntax
error, but I'm too new to know what it is.

Kevin Lohka

Re: Creating Functions & Triggers

From

Markus Bertheau

Date:

21 March 2004, 08:37:55

В Сбт, 20.03.2004, в 23:33, Kevin Lohka пишет:
> Hello everyone, I'm new to creating functions & triggers on postgresql
> and am having trouble creating a trigger to update a record with the
> modification date and the current user.
>
> My code is below, but doesn't work.  It hangs psql when I attempt to
> modify the record.  The function and trigger are created successfully.
>
> CREATE FUNCTION email_mod_date() RETURNS OPAQUE AS '
>    BEGIN
>          UPDATE email SET  m_date = current_date, m_by_user =
> current_user
>          WHERE id = NEW.id;
>
>    RETURN NULL;
>    END;
> 'LANGUAGE 'plpgsql';
>
>
>
> CREATE TRIGGER email_mod_date
> AFTER UPDATE
> ON email
> FOR EACH ROW
> EXECUTE PROCEDURE email_mod_date();

The trigger is probably recursively being called.
http://www.postgresql.org/docs/current/static/triggers.html#TRIGGER-DEFINITION

--
Markus Bertheau <twanger@bluetwanger.de>

Re: Creating Functions & Triggers

From

Tom Lane

Date:

21 March 2004, 15:18:37

Markus Bertheau <twanger@bluetwanger.de> writes:
> The trigger is probably recursively being called.

Well, of course.  Every UPDATE causes another UPDATE, which queues
another trigger firing.  What else would you expect but an infinite
loop?

The correct way to do this is illustrated in the plpgsql trigger
example at the bottom of this page:
http://www.postgresql.org/docs/7.4/static/plpgsql-trigger.html
You use a BEFORE trigger and alter the NEW record before it gets
written.

AFTER triggers are not intended for modifying data in the record they
are fired for --- it's too late for that.  (Even if you avoid the
infinite loop by testing whether you really need to do another UPDATE
or not, it's still ridiculously inefficient to force another cycle of
UPDATE when you could just as easily have gotten it right beforehand.)
Usually people use AFTER triggers for end-of-command consistency
checking or for propagating information to other tables.

            regards, tom lane

Re: Creating Functions & Triggers

From

Kevin Lohka

Date:

21 March 2004, 16:22:02

Thanks for the help Tom & Markus I've got it now.

Kevin Lohka

On Sunday, March 21, 2004, at 09:18 AM, Tom Lane wrote:

> Markus Bertheau <twanger@bluetwanger.de> writes:
>> The trigger is probably recursively being called.
>
> Well, of course.  Every UPDATE causes another UPDATE, which queues
> another trigger firing.  What else would you expect but an infinite
> loop?
>
> The correct way to do this is illustrated in the plpgsql trigger
> example at the bottom of this page:
> http://www.postgresql.org/docs/7.4/static/plpgsql-trigger.html
> You use a BEFORE trigger and alter the NEW record before it gets
> written.
>
> AFTER triggers are not intended for modifying data in the record they
> are fired for --- it's too late for that.  (Even if you avoid the
> infinite loop by testing whether you really need to do another UPDATE
> or not, it's still ridiculously inefficient to force another cycle of
> UPDATE when you could just as easily have gotten it right beforehand.)
> Usually people use AFTER triggers for end-of-command consistency
> checking or for propagating information to other tables.
>
>             regards, tom lane

Re: Creating Functions & Triggers

From

joseph speigle

Date:

21 March 2004, 17:05:28

On Sun, Mar 21, 2004 at 10:21:57AM -0700, Kevin Lohka wrote:
> Thanks for the help Tom & Markus I've got it now.
so did I so here is my version:

drop table email;
drop trigger email_mod_date;
drop function email_mod_date();
create table email (
id serial not null primary key,
email character varying(100),
name character varying(100),
m_date date,
m_by_user character varying(100));

 CREATE FUNCTION email_mod_date() RETURNS OPAQUE AS '
    BEGIN
          new.m_date = current_date;
        new.m_by_user = current_user;
        RETURN new;
    END;
 'LANGUAGE 'plpgsql';



 CREATE TRIGGER email_mod_date_trigger
 BEFORE UPDATE
 ON email
 FOR EACH ROW
 EXECUTE PROCEDURE email_mod_date();

 insert into email (email,name) values ('email1','name1');
 insert into email (email,name) values ('email2','name2');
 insert into email (email,name) values ('email3','name3');
select * from email;
update email set email='email1_new' where name='name1';
select * from email;

>
> Kevin Lohka
>
> On Sunday, March 21, 2004, at 09:18 AM, Tom Lane wrote:
>
> >Markus Bertheau <twanger@bluetwanger.de> writes:
> >>The trigger is probably recursively being called.
> >
> >Well, of course.  Every UPDATE causes another UPDATE, which queues
> >another trigger firing.  What else would you expect but an infinite
> >loop?
> >
> >The correct way to do this is illustrated in the plpgsql trigger
> >example at the bottom of this page:
> >http://www.postgresql.org/docs/7.4/static/plpgsql-trigger.html
> >You use a BEFORE trigger and alter the NEW record before it gets
> >written.
> >
> >AFTER triggers are not intended for modifying data in the record they
> >are fired for --- it's too late for that.  (Even if you avoid the
> >infinite loop by testing whether you really need to do another UPDATE
> >or not, it's still ridiculously inefficient to force another cycle of
> >UPDATE when you could just as easily have gotten it right beforehand.)
> >Usually people use AFTER triggers for end-of-command consistency
> >checking or for propagating information to other tables.
> >
> >            regards, tom lane
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
joe speigle
www.sirfsup.com