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

Re: 'on insert' rules and defaults

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Mark Hollomon" <mhh(at)nortelnetworks(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: 'on insert' rules and defaults
Date: 2000-04-06 20:35:00
Message-ID: 28623.955053300@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
"Mark Hollomon" <mhh(at)nortelnetworks(dot)com> writes:
> In other words, the default is not honored.

Right, since the INSERT written in the rule provides an explicit
specification of what should be inserted into t.  NEW.b is NULL
and that's what gets inserted.

> I also thought about COALESCE:

> CREATE RULE v_insert AS
> 	ON INSERT TO v DO INSTEAD
> 	INSERT INTO t values (NEW.i, COALESCE(NEW.b, false));

> But then two places have to know about the default value.

Another problem with that is that there's no way to specify insertion
of a NULL into b.

> Any other suggestions?

You really want default substitution to be done by the parser.
Any later is too late because you won't be able to tell an explicit
NULL from a defaulted column.

I haven't tried it, but I think it would work to declare the "view"
as a real table and then attach the rules to it:

CREATE TABLE t ( 
	i INTEGER,
	b BOOLEAN DEFAULT false
);

CREATE TABLE v ( 
	i INTEGER,
	b BOOLEAN DEFAULT false
);

CREATE RULE _RETv AS
	ON SELECT TO v DO INSTEAD
	SELECT * FROM t;

CREATE RULE v_insert AS
	ON INSERT TO v DO INSTEAD
	INSERT INTO t values ( NEW.i, NEW.b);

Then when you do

INSERT INTO v VALUES(43);

the default defined for v.b gets applied by the parser, before the
rule substitution happens.

This still means you have two places that know the default, but
since they're both table declarations maybe it's not so bad.

			regards, tom lane

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2000-04-06 21:42:31
Subject: Re: postgres crash on CURSORS
Previous:From: Mark HollomonDate: 2000-04-06 19:33:15
Subject: 'on insert' rules and defaults

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