Re: Views and default values

On Fri, Apr 06, 2001 at 01:26:39PM +0200, DaVinci wrote:
> On Fri, Apr 06, 2001 at 12:24:09PM +0100, Michael Ansley wrote:
> > >> Data Base:
> > >>
> > >> create table foo (
> > >> cod serial primary key,
> > >> explication text,
> > >> silly bool default 't');
> > >>
> > >> create view v_foo as
> > >> select * from foo;
> > >>
> > >> create rule v_foo_ins as on insert to v_foo do instead
> > >> insert into foo values (
> > >> NEW.cod,
> > >> NEW.explication,
> > >> NEW.silly);
> > You should probably have this:
> >
> > create rule v_foo_ins as on insert to v_foo do instead
> > insert into foo (explication, silly) values (
> > NEW.explication,
> > NEW.silly);
> >
> > The rule is trying to insert NEW.cod, which is null, into the table. This
> > rule let's the cod field take care of itself.
>
> That is valid for 'cod' but not for 'silly'. I'd like that if insert value
> is null, then I get default value, but if insert value is not null, this is
> value that is saved.

what would be NICE would be something that did

> select FIELD_DEFAULT(thistable.myfield);
't'::bool

> select FIELD_DEFAULT(afield) from sometable;
'now'::timestamp

> create view mynewview as select * from mytable;
> create rule myrule as on insert to mynewview
do instead
insert into mytable values (
coalesce(new.field1,FIELD_DEFAULT(mytable.field1)),
coalesce(new.field2,FIELD_DEFAULT(mytable.field2)),
coalesce(new.field3,FIELD_DEFAULT(mytable.field3)),
coalesce(new.field4,FIELD_DEFAULT(mytable.field4))
);

which would answer a question in a separate thread -- how to get
the default value of a column from a table's definition...

would it be difficult to create such a function?

--
americans should never read anything so subversive as what's at
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!