Thread: In RULEs, INSERT does not use DEFAULTs
Folks, At one time, INSERTing a NULL into a column with a DEFAULT used to INSERT the DEFAULT. Is there some way to get this behavior back? I think that this is a bug introduced by removing the NULL -> DEFAULT behavior and not replacing it somehow. Below is a simple repro. I think this is a bug. Although there are workarounds, the ones I've found have been clumsy and foot-gun-ish. Is there some way to fix this? Cheers, D CREATE TABLE foo ( foo_id SERIAL PRIMARY KEY , foo_truth BOOLEAN NOT NULL DEFAULT TRUE , foo_text TEXT ); CREATE VIEW foo_caps AS SELECT foo_id, foo_truth, UPPER(foo_text) AS "foo_text_cap" FROM foo; /* The naïf way */ CREATE RULE foo_caps_insert AS ON INSERT TO foo_caps DO INSTEAD INSERT INTO foo (foo_id, foo_truth, foo_text) VALUES (NEW.foo_id, NEW.foo_truth, NEW.foo_text_cap); INSERT INTO foo_caps (foo_truth) VALUES (false); ERROR: null value in column "foo_id" violates not-null constraint /* Possibly less naïf, but... */ DROP RULE foo_caps_insert ON foo_caps; CREATE RULE foo_caps_insert AS ON INSERT TO foo_caps DO INSTEAD INSERT INTO foo ( foo_id, foo_truth, foo_text ) VALUES ( COALESCE(NEW.foo_id, DEFAULT), COALESCE(NEW.foo_truth, DEFAULT), NEW.foo_text_cap ); ERROR: syntax error at or near "DEFAULT" at character 183 LINE 10: COALESCE(NEW.foo_id, DEFAULT), ^ -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
David Fetter <david@fetter.org> writes:
> At one time, INSERTing a NULL into a column with a DEFAULT used to
> INSERT the DEFAULT. Is there some way to get this behavior back?
PG has *never* done that in any version that I can recall, and it isn't
likely that we would install such an obvious violation of the SQL spec.
The correct way to get the behavior you are after is to attach a default
to the view's column (ALTER view ALTER col SET DEFAULT ...)
regards, tom lane
On Sun, Jun 12, 2005 at 10:56:22PM -0400, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > At one time, INSERTing a NULL into a column with a DEFAULT used to > > INSERT the DEFAULT. Is there some way to get this behavior back? > > PG has *never* done that in any version that I can recall, and it > isn't likely that we would install such an obvious violation of the > SQL spec. > The correct way to get the behavior you are after is to attach a > default to the view's column (ALTER view ALTER col SET DEFAULT ...) With all due respect, that's a giant foot gun in terms of maintenance, i.e. making a single behavior depend on two things that can easily get out of sync. With hand-altered DEFAULTs, there's no way to alter the DEFAULTs on the the base TABLE and have those changes propagate, as people would usually want it to. I can see how somebody might want a VIEW's column to have a DEFAULT that was different from the column to which it refers in some specific case. However, "defaults to different from the underlying column's DEFAULT" is a Bad Thing(TM). I believe this isn't just my problem. Without access to a the underlying column's DEFAULT, how can people implement the automated WRITEable VIEWs? Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
David Fetter <david@fetter.org> writes:
> I believe this isn't just my problem. Without access to a the
> underlying column's DEFAULT, how can people implement the automated
> WRITEable VIEWs?
That's a reasonable question, but translating "insert null" to "insert
the default" is not a reasonable answer.
There was some speculation just a couple days ago about inventing a
function that would compute the default associated with some other
table's column, but it's not clear how to make that work (in
particular, how to declare the result type of such a function).
Another possibility is a command along the lines ofALTER view ALTER col LINK DEFAULT TO othertable.col;
(syntax open to argument of course) which accomplishes the
same thing without having to figure a way to avoid the constraints
of a specific function result type.
regards, tom lane
On Sun, 2005-06-12 at 20:33 -0700, David Fetter wrote:
> On Sun, Jun 12, 2005 at 10:56:22PM -0400, Tom Lane wrote:
> > David Fetter <david@fetter.org> writes:
> > > At one time, INSERTing a NULL into a column with a DEFAULT used to
> > > INSERT the DEFAULT. Is there some way to get this behavior back?
> >
> > PG has *never* done that in any version that I can recall, and it
> > isn't likely that we would install such an obvious violation of the
> > SQL spec.
>
> > The correct way to get the behavior you are after is to attach a
> > default to the view's column (ALTER view ALTER col SET DEFAULT ...)
>
> With all due respect, that's a giant foot gun in terms of maintenance,
> i.e. making a single behavior depend on two things that can easily
> get out of sync. With hand-altered DEFAULTs, there's no way to alter
> the DEFAULTs on the the base TABLE and have those changes propagate,
> as people would usually want it to.
Change the table and view to use a domain.
CREATE DOMAIN tabviewtype_x AS integer DEFAULT 12;
Altering the default of the domain will bump both the table and view
defaults, or anywhere else that type happens to be used.
--
Rod Taylor <pg@rbt.ca>
On Sun, Jun 12, 2005 at 11:48:29PM -0400, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > I believe this isn't just my problem. Without access to a the > > underlying column's DEFAULT, how can people implement the automated > > WRITEable VIEWs? > > That's a reasonable question, but translating "insert null" to "insert > the default" is not a reasonable answer. Right. My mistake. > There was some speculation just a couple days ago about inventing a > function that would compute the default associated with some other > table's column, but it's not clear how to make that work (in > particular, how to declare the result type of such a function). I was looking into polymorphic functions for this. At least in PL/PgSQL (which isn't a general solution, but I was looking for my own solution) there is a way to get the return type based on the input types. > Another possibility is a command along the lines of > ALTER view ALTER col LINK DEFAULT TO othertable.col; I still think that something like this (i.e. the VIEWs column has the same default as its underlying base TABLE or VIEW, where there is one) should be the default behavior. Principle of Least Astonishiment. > (syntax open to argument of course) which accomplishes the > same thing without having to figure a way to avoid the constraints > of a specific function result type. It'd be nice to have one of these :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
On 6/12/05, Tom Lane <tgl@sss.pgh.pa.us> wrote: > David Fetter <david@fetter.org> writes: > > I believe this isn't just my problem. Without access to a the > > underlying column's DEFAULT, how can people implement the automated > > WRITEable VIEWs? > > That's a reasonable question, but translating "insert null" to "insert > the default" is not a reasonable answer. > > There was some speculation just a couple days ago about inventing a > function that would compute the default associated with some other > table's column, but it's not clear how to make that work (in > particular, how to declare the result type of such a function). > I discarded the idea because i couldn't fight with the polymorphic function to return the correct value in any case. But i successfully found that hacking rewriteHandler.c can do the trick. I am using that in updateable views project. > Another possibility is a command along the lines of > ALTER view ALTER col LINK DEFAULT TO othertable.col; > (syntax open to argument of course) which accomplishes the > same thing without having to figure a way to avoid the constraints > of a specific function result type. > That's sounds like a good idea too -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;)