Re: column default dependant on another columns value

> -----Mensaje original-----
> De: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] En nombre de Tom Lane
> Enviado el: Martes, 01 de Julio de 2008 19:24
> Para: Fernando Hevia
> CC: 'Richard Broersma'; pgsql-sql(at)postgresql(dot)org
> Asunto: Re: [SQL] column default dependant on another columns value
>
> "Fernando Hevia" <fhevia(at)ip-tel(dot)com(dot)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.