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@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!