> -----Mensaje original-----
> De: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] En nombre de Tom Lane
> Enviado el: Martes, 01 de Julio de 2008 19:24
> Para: Fernando Hevia
> CC: 'Richard Broersma'; pgsql-sql@postgresql.org
> Asunto: Re: [SQL] column default dependant on another columns value
>
> "Fernando Hevia" <fhevia@ip-tel.com.ar> writes:
> > Anyway, the rule didn't work. Got "an infinite recursion
> error" when
> > inserting on the table.
> > Can't figure out where the recursion is
>
> You didn't show us the rule, but I imagine that you think the
> WHERE clause is applied while expanding the rule. It's not,
> it can only suppress rows at run-time; and what you've got is
> infinite macro expansion recursion.
>
I see. In that case rules do not serve this particular purpose.
It seems a trigger should be the tool for solving this.
Just to confirm, this is my test case:
create table table1 ( column1 text, seconds integer, minutes integer );
CREATE RULE "my_rule" AS ON
INSERT TO table1
WHERE minutes is null
DO INSTEAD
INSERT INTO table1 (column1, seconds, minutes) VALUES(new.column1,
new.seconds, new.seconds/60);
insert into table1 values ('a', 60); --- Here the rule should kick
in right?
insert into table1 values ('b', 120, NULL); --- Rule should kick in too.
insert into table1 values ('c', 180, 3); --- the rule should not apply
since minutes is not null.
Of course, all three of them throw the recursion error.