Re: automatically updated an attribute with the current time - Mailing list pgsql-novice

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
>