"Piergiorgio Buongiovanni" <piergiorgio.buongiovanni@netspa.it> writes:
> We are experiencing a problem on using rules and serial data types with
> PostgreSQL.
> We are trying to align two tables in two different schemas through rules and
> the main table has a column of data type SERIAL.
> ...
> Now we create a rule in order to align l10n.prova when a row is inserted in
> business.prova:
> CREATE OR REPLACE RULE _Prova_Ins AS ON INSERT
> TO business.Prova
> DO ALSO INSERT INTO l10n.Prova VALUES (NEW.iSId, NEW.cName);
By and large, rules don't work very well with commands involving
volatile functions like nextval(). That's because a rule is a macro
and the macro expansion can result in multiple calls of the volatile
function.
Best advice is to use a trigger, not a rule, for propagating changes
from one table to another.
regards, tom lane