Received: from ara.zf.jcu.cz (ara.zf.jcu.cz [160.217.161.4]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1EChZE47860 for ; Thu, 14 Feb 2002 07:44:43 -0500 (EST) (envelope-from zakkr@zf.jcu.cz) Received: from ara.zf.jcu.cz (LOCALHOST [127.0.0.1]) by LOCALHOST (8.12.0.Beta7/8.12.0.Beta7/Debian 8.12.0.Beta7-1) with ESMTP id g1EChI65010300; Thu, 14 Feb 2002 13:43:18 +0100 Received: (from zakkr@localhost) by ara.zf.jcu.cz (8.12.0.Beta7/8.12.0.Beta7/Debian 8.12.0.Beta7-1) id g1EChIob010299; Thu, 14 Feb 2002 13:43:18 +0100 Date: Thu, 14 Feb 2002 13:43:18 +0100 From: Karel Zak To: Graham Vickrage Cc: Postgres SQL Subject: Re: trigger on table Message-ID: <20020214134318.A8046@zf.jcu.cz> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: ; from graham@digitalplanit.com on Thu, Feb 14, 2002 at 12:17:02PM -0000 X-Archive-Number: 200202/154 X-Sequence-Number: 6581 On Thu, Feb 14, 2002 at 12:17:02PM -0000, Graham Vickrage wrote: > I am trying to create a trigger on a table that simply sets the last_updated > field when any updates are made to that table. > > I have tried the following: - > > CREATE FUNCTION set_item_last_updated () RETURNS OPAQUE AS ' > BEGIN > UPDATE item SET last_updated = now(); ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ > RETURN OLD; > END;' LANGUAGE 'plpgsql'; > > CREATE TRIGGER item_last_updated AFTER UPDATE ON item ^^^^^^^ > FOR EACH ROW EXECUTE PROCEDURE set_item_last_updated(); > > When I try to execute this it hangs and postmaster eventually runs out of > memory. You create trigger on update and inside this trigger you do update again. It's cycle... > Is there a way to do it just using sql not plpsql? It works with PL/SQL, try: CREATE FUNCTION set_item_last_updated () RETURNS OPAQUE AS ' BEGIN NEW.last_updated := ''now''; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER item_last_updated BEFORE UPDATE ON item FOR EACH ROW EXECUTE PROCEDURE set_item_last_updated (); Karel -- Karel Zak http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz