From: Reiner Dassing <dassing@wettzell.ifag.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Triggers do not fire
Date: Wed, 17 Oct 2001 17:29:37 +0200
Message-ID: <3BCDA3E1.96B027EA@wettzell.ifag.de> (raw)
References: <3BCC2CBB.9C3A31EF@wettzell.ifag.de>
<22705.1003268168@sss.pgh.pa.us>
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@wettzell.ifag.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dassing@wettzell.ifag.de, tgl@sss.pgh.pa.us
Subject: Re: Triggers do not fire
In-Reply-To: <3BCDA3E1.96B027EA@wettzell.ifag.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox