Re: Triggers do not fire

Hello Tom!

Thank you for your help!
Your hints did solve the asked problem.
The update trigger is fired if there is a row in the table test to be
updated.

But, behind my question there was another design (philosophie) which I
am trying to
solve by the means of different triggers.
This is off topic, but ...

Maybe, you or somebody on the list can give a hint how solve the
following task:

I have a table which has a lot of entries (some x millions) of the kind
(id, timestamp, value)
The access (selects) is concentrated to the timely last some thousands
entries.
To adapt this fact I want to setup a "virtual" table - test in my
example - which
is accessed by the clients but in reality the entries are separated to
different small
tables. These table are dynamically created to hold the values
distinguished by years.
By the use of triggers I can redirect inserts from the table test to
small tables,
called test_x_y with x = id, y=year.
To update an entry the value in the main table must exist, therefore,
this approach does not work.

Rules do also not work as I must dynamically build table names.
And the execution of pgsql functions is not possible in rules. Correct?

Another possible approach would be to make selects which give back
results to be
used as table names.
I.e., it would be necessary to have something like
create table f(NEW.val) as select ....
where f(...) gives back a name of a table.

Tom Lane wrote:
>
> Reiner Dassing <dassing(at)wettzell(dot)ifag(dot)de> writes:
> > I have written a very small test procedure to show a possible error
> > on PostgreSQL V7.1.1.
>
> The error is yours: you set up the trigger function to return NULL,
> which means it's telling the system not to allow the INSERT or UPDATE.
>
> > INSERT INTO test VALUES(1,'2000-10-11 12:00:00',-20.2);
> > NOTICE: Fired INSERT
> > INSERT 0 0
>
> Note the summary line saying that zero rows were inserted.
>
> > UPDATE test SET value = 1000.0 WHERE epoch = '2000-10-11 12:10:00' AND
> > sensor_id = 1;
> > UPDATE 0
>
> Here, zero rows were updated, so of course there was nothing to fire
> the trigger on.
>
> regards, tom lane

--
Mit freundlichen Gruessen / With best regards
Reiner Dassing