<br><br><div><span class="gmail_quote">On 8/14/07, <b class="gmail_sendername">Kevin C. Smith</b> <<a href="mailto:kevin@linuxsmith.com">kevin@linuxsmith.com</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
><br>> CREATE FUNCTION set_last_update() RETURNS opaque AS '<br>> BEGIN<br>> NEW.last_updated:=now();<br>> RETURN NEW;<br>> END;<br>> ' LANGUAGE 'plpgsql';<br>><br>> CREATE TRIGGER set_last_update AFTER INSERT OR UPDATE ON people FOR EACH
<br>> ROW<br>> EXECUTE PROCEDURE set_last_update();<br>><br><br>Okay I see maybe two issues (disclaimer I don't know what the hell I'm<br>talking<br>about).<br><br>Wouldn't a trigger AFTER insert or update become in infinite loop, and maybe
<br>postgresql therefore ignores it? Just a guess.</blockquote><div><br>I thought trigger updates were different, but I guess it's possible. <br></div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
I also think that were you have 'opaque' you need 'trigger'.</blockquote><div><br><br>The 'trigger' type caused the parser to complain. However, I'll try it again when I can. It's possible the other things I did caused 'trigger' to be valid. But my guess is 'opaque' works in
7.1, but 'trigger' was added later.<br></div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">I tested this and it worked:<br><br>
<br>CREATE OR REPLACE FUNCTION set_last_update()<br> RETURNS "trigger" AS<br>BEGIN<br> NEW.last_updated = now();<br> RETURN NEW;<br>END;<br> LANGUAGE 'plpgsql' VOLATILE;<br><br><br>CREATE TRIGGER set_last_update
<br> BEFORE INSERT OR UPDATE<br> ON test_table<br> FOR EACH ROW<br> EXECUTE PROCEDURE set_last_update();</blockquote><div><br><br>I chose AFTER because I didn't want the timestamp to be affected if for some reason the update failed. I also thought if it happened before the insert, what would it be updating, since the record didn't exist yet.
<br><br></div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Good luck<br><br>--<br>Kevin C. Smith<br><br>_______________________________________________
<br>Cialug mailing list<br><a href="mailto:Cialug@cialug.org">Cialug@cialug.org</a><br><a href="http://cialug.org/mailman/listinfo/cialug">http://cialug.org/mailman/listinfo/cialug</a><br></blockquote></div><br><br clear="all">
<br>-- <br>Tim