Skip site navigation (1) Skip section navigation (2)

Re: column default dependant on another columns value

From: "Fernando Hevia" <fhevia(at)ip-tel(dot)com(dot)ar>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: column default dependant on another columns value
Date: 2008-07-02 13:52:02
Message-ID: 009c01c8dc4a$ce0f29d0$8f01010a@iptel.com.ar (view raw or flat)
Thread:
Lists: pgsql-sql
 

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


In response to

pgsql-sql by date

Next:From: Dhanushka SamarakoonDate: 2008-07-02 17:12:37
Subject: Re: Need a sample Postgre SQL script
Previous:From: Tom LaneDate: 2008-07-01 22:24:24
Subject: Re: column default dependant on another columns value

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group