Thread: Views and default values

Views and default values

From
DaVinci
Date:
 Hello.

 I have a problem (one more ;) with views, rules for inserting and default
 values.

 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);

 What is my problem with this?... It is easy to make insert in 'foo' with
 default value:

     # insert into foo(explication) values ('Because is in love');

 But, what about 'v_foo'? If I make:

     # insert into v_foo(explication) values ('Because have not slept');

 I don't get default values properly because insert rule is forcing null
 values. In fact, in this example I don't get nothing except an error for
 trying to insert null value in primary key field.

 Is there any way of resolving this problem?. Perhaps NEW might pass a
 'default' value when it doesn't receive any data? have I lost some
 important concept?...

 Thanks.

                                 David

Re: Views and default values

From
DaVinci
Date:
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.

 Any idea?...

 Thanks.

                                             David

RE: Views and default values

From
Michael Ansley
Date:

I think that a coalesce might work here, something like:

create rule v_foo_ins as on insert to v_foo do instead
        insert into foo (explication, silly) values (
                NEW.explication,
                COALESCE(NEW.silly, 'DEFAULT_VALUE'));

Obviously you replace the DEFAULT_VALUE with whatever you want your default to be.

Cheers...

MikeA

>> -----Original Message-----
>> From: DaVinci [mailto:bombadil@wanadoo.es]
>> Sent: 06 April 2001 12:27
>> To: pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] Views and default values
>>
>>
>> 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.
>>
>>  Any idea?...
>>
>>  Thanks.
>>
>>                                                             
>>                      David
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 4: Don't 'kill -9' the postmaster
>>

_________________________________________________________________________
This e-mail and any attachments are confidential and may also be privileged and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an
intended or authorised recipient of this e-mail or have received it in error, please delete
it immediately and notify the sender by e-mail. In such a case, reading, reproducing,
printing or further dissemination of this e-mail is strictly prohibited and may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free
from computer viruses or other defects. The opinions expressed in this e-mail and any
attachments may be those of the author and are not necessarily those of Intec Telecom
Systems PLC.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
__________________________________________________________________________

Re: Views and default values

From
Tom Lane
Date:
Michael Ansley <Michael.Ansley@intec-telecom-systems.com> writes:
> I think that a coalesce might work here, something like:

> create rule v_foo_ins as on insert to v_foo do instead
>     insert into foo (explication, silly) values (
>         NEW.explication,
>         COALESCE(NEW.silly, 'DEFAULT_VALUE'));

> Obviously you replace the DEFAULT_VALUE with whatever you want your default
> to be.

This isn't really a good solution, because it prevents explicit
insertion of a NULL (which isn't something that DaVinci cares about
in the particular example, but it would be annoying in related cases).
Also, you might not want your rule to have to know just what default
value the table has.

There is a better answer: INSERT's default-value-addition machinery
operates upstream of the rule rewriter, so it could work for a view.
Unfortunately the system won't accept
    ALTER TABLE v_foo ALTER COLUMN silly SET DEFAULT 't'
but I see no real good reason why it shouldn't.  DaVinci could either
remove the restriction against doing this (quick code hack) or insert
the appropriate pg_attrdef entry by hand (ugly...).

Meanwhile, if Bruce is paying attention, please add a TODO list item
to allow column defaults to be set on views.  (Please note pg_dump
would need to be fixed to know it needs to dump these, too.)

            regards, tom lane

Re: Views and default values

From
will trillich
Date:
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!