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