<br><br><div><span class="gmail_quote">On 8/13/07, <b class="gmail_sendername">Josh More</b> <<a href="mailto:morej@alliancetechnologies.net">morej@alliancetechnologies.net</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;">
Are you defining your functions before you call them as triggers? I<br>remember having that issue when I did this years ago.</blockquote><div><br><br>I tried creating the trigger first, but it said the function didn't exist.
<br>
<br>
Josh suggested these 2 commands off list:<br>
<br>
create function plpgsql_call_handler () returns opaque as '/usr/lib/pgsql/plpgsql.so' language 'C';<br>
create trusted language 'plpgsql' handler plpgsql_call_handler lancompiler 'PL/pgSQL';<br>
<br>
After executing those 2 commands, I was able to create the
function and the trigger. But when I update the database, the
last_updated field isn't set. Here's what I have:<br>
<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 ROW EXECUTE PROCEDURE set_last_update();<br><br><span class="gmail_quote">I've
tried changing now() to ''now'' (2 single quotes before and after) like
it shows in the postgres documentation online, but that doesn't work.
I tried changing it to double quotes, and I got an unterminated quote
error.<br>
<br>
I'm sure there's something simple I'm missing.<br><br><br></span> <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;">
-Josh More, RHCE, CISSP, NCLP, GIAC<br> <a href="mailto:morej@alliancetechnologies.net">morej@alliancetechnologies.net</a><br> 515-245-7701<br><br>>>> "Tim Wilson" <<a href="mailto:tim_linux@wilson-home.com">
tim_linux@wilson-home.com</a>> 08/13/07 11:01 PM >>><br>That's one of the examples I was talking about. The function that is<br>created in the example returns type "trigger" (quotes added for<br>
emphasis).<br>When I try that in my version, it says 'parse error at or near<br>"trigger"'.<br>Another example said to use void, another said opaque. All caused the<br>parser to complain.<br><br>On 8/13/07, Kevin C. Smith <
<a href="mailto:kevin@linuxsmith.com">kevin@linuxsmith.com</a>> wrote:<br>><br>> On Mon, 2007-08-13 at 22:02 -0500, Tim Wilson wrote:<br>> > I'm using Postgres 7.1.3, and I'm trying to set up a trigger. I
<br>have<br>> > a last_updated column on all of my tables, that is of type<br>timestamp.<br>> > I want to set the field every time the record is updated. I thought<br>a<br>> > trigger would be best for that, but every example I find via Google
<br>> > doesn't work. One said to create a function that returned type<br>> > TRIGGER, another said type VOID, neither of which worked. Every<br>> > example I find uses one of those return types. Anyone have any
<br>> > ideas?<br>><br>> I haven't tried triggers with postgresql, but I think it's a two<br>> step thing in most cases. Write a function, then create a trigger<br>> to call that function on insert or update.
<br>><br>> Try:<br>> <a href="http://www.postgresql.org/docs/8.2/static/triggers.html">http://www.postgresql.org/docs/8.2/static/triggers.html</a><br>> <a href="http://www.postgresql.org/docs/8.2/static/plpgsql.html">
http://www.postgresql.org/docs/8.2/static/plpgsql.html</a><br>><br>> Religion is regarded by the common people as true, by the wise as<br>false,<br>> and by the rulers as useful. --- Lucius Annaeus Seneca<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>><br><br><br><br>--<br>Tim<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