Re: column default dependant on another columns value

 

> -----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.