RE: Views and default values - Mailing list pgsql-general

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