Thread: automatically updated an attribute with the current time

Hello,

I am trying to automatically update an attribute with the current time.

The attribute is a modify-attribute and holds the last modification time of
each row in the table "contacts". If a row is updated the modify-value of
the attribute, should be changed with the current time.

I have tried something myself. I've tried to add a trigger, which calls a
function. Here is what I have tried:

CREATE FUNCTION update_function (int4)
RETURNS int4 (<-- I don't want any return values, I don't see why this is
necessary)
AS 'update contact SET modify = (timestamp(now())) where name = $1;
select id from contact where id = $1;' (<-- I’ve read the an function should
end in an select because of the return value)
LANGUAGE 'SQL';

CREATE TRIGGER update_trigger
AFTER UPDATE ON contacts FOR EACH ROW
EXECUTE PROCEDURE update_function ('id') (<-- I use the id to identify the
row.. I’m not sure if this is necessary)

Unfortunately what I have tried doesn't work. I first insert the function
and after that I insert the trigger. The trigger complains about the
function not existing. When I try to create a function/trigger without input
parameters I get complains the result should be opaque.

I hope someone can help me with this problem. I am new to functions and
triggers. If someone tells me what I am doing wrong, it will be greatly
appreciated.

Thanks in advance.

Mark Bleeker

Mark,

a few points:

o    for a trigger function the return value of the function needs to be
opaque.
o    your defining the function with an input parameter of type int4 and
then passing in a text.
o    you don't need to pass in a parameter as you can refer to the OLD and
NEW records in the trigger so all of the fields in the affected table are
already available.

See
http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=10
for code that should write these functions / triggers for you automatically
(note: I didn't write and haven't tried these functions but it looks ok)

hih

steve boyle

----- Original Message -----
From: "Mark Bleeker" <mark@trilab.com>
To: <pgsql-novice@postgresql.org>
Sent: Tuesday, January 22, 2002 11:02 AM
Subject: [NOVICE] automatically updated an attribute with the current time


> Hello,
>
> I am trying to automatically update an attribute with the current time.
>
> The attribute is a modify-attribute and holds the last modification time
of
> each row in the table "contacts". If a row is updated the modify-value of
> the attribute, should be changed with the current time.
>
> I have tried something myself. I've tried to add a trigger, which calls a
> function. Here is what I have tried:
>
> CREATE FUNCTION update_function (int4)
> RETURNS int4 (<-- I don't want any return values, I don't see why this is
> necessary)
> AS 'update contact SET modify = (timestamp(now())) where name = $1;
> select id from contact where id = $1;' (<-- I've read the an function
should
> end in an select because of the return value)
> LANGUAGE 'SQL';
>
> CREATE TRIGGER update_trigger
> AFTER UPDATE ON contacts FOR EACH ROW
> EXECUTE PROCEDURE update_function ('id') (<-- I use the id to identify the
> row.. I'm not sure if this is necessary)
>
> Unfortunately what I have tried doesn't work. I first insert the function
> and after that I insert the trigger. The trigger complains about the
> function not existing. When I try to create a function/trigger without
input
> parameters I get complains the result should be opaque.
>
> I hope someone can help me with this problem. I am new to functions and
> triggers. If someone tells me what I am doing wrong, it will be greatly
> appreciated.
>
> Thanks in advance.
>
> Mark Bleeker
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

On 22 Jan 2002 at 12:02, Mark Bleeker wrote:
> Hello,
>
> I am trying to automatically update an attribute with the current time.
<snip>

from one newbie to another :)

i just figured it out yesterday:

-------------------------------------------------------
-- this is a templatetable, other tables inherit their
-- audit columns from it
drop table au_col;
create table au_col (
  mut_id varchar(100) not null default current_user,
  mut_timestamp timestamp not null default CURRENT_TIMESTAMP
);

-- function to change the values
drop function au_col();
create function au_col()
returns opaque
as  'begin
      old.mut_id = current_user;
      old.mut_timestamp = CURRENT_TIMESTAMP;
      return old;
    end;'
language 'plpgsql';

-- trigger to call the funtcion
drop trigger au_col on au_col;
create trigger au_col
  before update or delete -- create is covered by defaults
  on au_col
  for each row
  execute procedure au_col();
-------------------------------------------------------

HTH, HAND

--
Jules Alberts

Hi,

Thanks Steve and Jules, I have solved the problem with your help :)

I appreciate it very much!

Mark Bleeker

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Jules Alberts
Sent: woensdag 23 januari 2002 12:28
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] automatically updated an attribute with the
current time


On 22 Jan 2002 at 12:02, Mark Bleeker wrote:
> Hello,
>
> I am trying to automatically update an attribute with the current time.
<snip>

from one newbie to another :)

i just figured it out yesterday:

-------------------------------------------------------
-- this is a templatetable, other tables inherit their
-- audit columns from it
drop table au_col;
create table au_col (
  mut_id varchar(100) not null default current_user,
  mut_timestamp timestamp not null default CURRENT_TIMESTAMP
);

-- function to change the values
drop function au_col();
create function au_col()
returns opaque
as  'begin
      old.mut_id = current_user;
      old.mut_timestamp = CURRENT_TIMESTAMP;
      return old;
    end;'
language 'plpgsql';

-- trigger to call the funtcion
drop trigger au_col on au_col;
create trigger au_col
  before update or delete -- create is covered by defaults
  on au_col
  for each row
  execute procedure au_col();
-------------------------------------------------------

HTH, HAND

--
Jules Alberts

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

> create function au_col()
> returns opaque
> as  'begin
>       old.mut_id = current_user;
>       old.mut_timestamp = CURRENT_TIMESTAMP;
>       return old;
>     end;'
> language 'plpgsql';

As a note, while this works in your case, because you're changing
all the columns inside the trigger, in general, you want to be making
modifications to NEW and returning NEW on updates unless you
want to ignore the actual sets done by the update statement (which is
sometimes what you want).


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com