Re: automatically updated an attribute with the current time
От | Steve Boyle \(Roselink\) |
---|---|
Тема | Re: automatically updated an attribute with the current time |
Дата | |
Msg-id | 001d01c1a390$9b4a9920$c55869d5@dualtower обсуждение исходный текст |
Ответ на | automatically updated an attribute with the current time ("Mark Bleeker" <mark@trilab.com>) |
Список | 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 >
В списке pgsql-novice по дате отправления: