Re: simple auto-updating timestamp ?

Andreas wrote:

> D. Dante Lorenso wrote:
>
>> You can do this by adding a trigger to your table. Just define the
>> trigger
>> to be invoked on INSERT and UPDATE for your table. The trigger
>> definition
>> would look something like this: [...]
>
>
> Thanks.
> So far that works for one table.
>
> Can I have this behaviour somehow inherited by child-tables ?
> Like:
> CREATE TABLE objects (
> id integer primary key,
> created_ts timestamp(0) DEFAULT LOCALTIMESTAMP,
> update_ts timestamp(0),
> deleted_ts timestamp(0), -- things get ignored in normal
> processing
> ...
> );
>
> Then create a trigger as in your example that updates this timestamp.
> Every other table in the db would inherit (objects) to get those
> standard fields that I'd like to have everywhere. It'd be nice not
> having to bother about the "methods" of the objects-class for every
> child-class.

Yeah I know what you mean. Someone jump in here and correct me if I'm
wrong,
but I don't believe that triggers are inherited in PG. Of course, you
already
have the 'set_update_ts' function defined, so you would only have to declare
the trigger for every child table (not the function).

Verify that this is true. Last time I checked i think that's how it worked.

>> CREATE FUNCTION "public"."set_update_ts" () RETURNS trigger AS'
>> BEGIN
>> NEW.update_ts = NOW();
>> RETURN NEW;
>> END; 'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY
>> INVOKER;
>
>
> I entered your code into psql and checked it afterwards with pgadmin3.
> pgadmin shows some parts different to the code that I pushed through
> psql :
> 1) create OR REPLACE ...
> 2) immuntable; <-- End of line What does this part behind
> "immutable" do ?

You probably want to remove the 'IMMUTABLE CALLED ON NULL INPUT SECURITY
INVOKER'.
That was my cut-and-paste error. I meant to strip that off for you.
Here's the
page that explains what all those do, though:

http://www.postgresql.org/docs/7.4/static/sql-createfunction.html

Dante