Thread: Column defaults fail with rules on view
I'm finding that column defaults are not being assigned to nulls when I
do an insert by way of a an ON INSERT rule on a view. For example, the
following script
\set ON_ERROR_STOP ON
\c template1
--DROP DATABASE testdb;
CREATE DATABASE testdb;
\c testdb
create table test_table (
field1 char(1) not null,
field2 serial,
field3 integer default 1,
field4 varchar(24) default '(default value)',
constraint testdb_pkey primary key (field2));
INSERT INTO test_table VALUES ('A');
SELECT * FROM test_table;
CREATE VIEW test_table_v AS
SELECT field1, field3, field4 FROM test_table;
SELECT * FROM test_table;
CREATE RULE test_table_rd AS ON DELETE TO test_table_v DO INSTEAD
DELETE FROM test_table WHERE field1 = old.field1;
CREATE RULE test_table_ri AS ON INSERT TO test_table_v DO INSTEAD
INSERT INTO test_table (field1, field3, field4)
VALUES (new.field1, new.field3, new.field4);
CREATE RULE test_table_ru AS ON UPDATE TO test_table_v DO INSTEAD
UPDATE test_table SET
field1 = new.field1,
field3 = new.field3,
field4 = new.field4
WHERE field1 = old.field1;
INSERT INTO test_table_v VALUES ('B');
SELECT * FROM test_table;
-- produces this output
CREATE DATABASE
CREATE TABLE
INSERT 147461 1
field1 | field2 | field3 | field4
--------+--------+--------+-----------------
A | 1 | 1 | (default value)
(1 row)
-- above works fine, but then
CREATE VIEW
field1 | field2 | field3 | field4
--------+--------+--------+-----------------
A | 1 | 1 | (default value)
(1 row)
CREATE RULE
CREATE RULE
CREATE RULE
INSERT 147468 1
field1 | field2 | field3 | field4
--------+--------+--------+-----------------
A | 1 | 1 | (default value)
B | 2 | |
(2 rows)
-- notice how field3 and field4 are not assigned their defaults for row B!
Is this supposed to work that way? I would expect field3 and field4 to
have their respective column defaults assigned on the second INSERT (row
B), just like on the first INSERT (row A).
wassup wit dat?
~Berend Tober
On Friday 19 September 2003 09:00, btober@seaworthysys.com wrote:
> I'm finding that column defaults are not being assigned to nulls when I
> do an insert by way of a an ON INSERT rule on a view. For example, the
> following script
[snip]
> CREATE RULE test_table_ri AS ON INSERT TO test_table_v DO INSTEAD
> INSERT INTO test_table (field1, field3, field4)
> VALUES (new.field1, new.field3, new.field4);
[snip]
> Is this supposed to work that way? I would expect field3 and field4 to
> have their respective column defaults assigned on the second INSERT (row
> B), just like on the first INSERT (row A).
Hmm - well, you're explicitly telling it to insert VALUES (..., new.field3,
...) so if new.field3 is null then it *should* do that.
Now - how you should go about getting the default I don't know. You could
build a rule with WHERE NEW.field3 IS NULL and then not pass field3, but that
would stop you explicitly setting it to null.
Out of curiosity, can you tell me what happens if you insert into the view
('C',DEFAULT,DEFAULT)?
--
Richard Huxton
Archonet Ltd
> On Friday 19 September 2003 09:00, btober@seaworthysys.com wrote:
>> I'm finding that column defaults are not being assigned to nulls when
>> I do an insert by way of a an ON INSERT rule on a view. For example,
>> the following script
> [snip]
>
> Hmm - well, you're explicitly telling it to insert VALUES (...,
> new.field3, ...) so if new.field3 is null then it *should* do that.
I (apparently mistakenly) thought that the point of specifying DEFAULT
values in the table column definition was so that the default value would
be inserted automatically rather than a null. And anyway, that IS how it
seems to work when I do the insert for row A to the table directly with
INSERT INTO test_table VALUES ('A');
where fields 2, 3, and 4 have not been assigned values. Why do they get
the default in this case?
>
> Now - how you should go about getting the default I don't know. You
> could build a rule with WHERE NEW.field3 IS NULL and then not pass
> field3, but that would stop you explicitly setting it to null.
My work-around has been to define BEFORE INSERT triggers with lines like
SELECT INTO new.field3 COALESCE(new.field3, 1);
testing for and optionally assigning the default, but I really don't like
having to explicitly do that for every table and NOT NULL column, since I
make pretty much routine use of RULES on VIEWS to make writeable views
the interface to my user application.
>
> Out of curiosity, can you tell me what happens if you insert into the
> view ('C',DEFAULT,DEFAULT)?
> Richard Huxton
Same script, but with
INSERT INTO test_table VALUES ('A');
INSERT INTO test_table_v VALUES ('B');
INSERT INTO test_table_v VALUES ('C', DEFAULT, DEFAULT);
gives
field1 | field2 | field3 | field4
--------+--------+--------+-----------------
A | 1 | 1 | (default value)
B | 2 | |
C | 3 | |
(3 rows)
-- so no change in behavior. I notice that field2, which was declared
type SERIAL, and so also has a DEFAULT, but one which calls the nextval
function rather than simply assigning a value, gets its default value
assigned in both the table insert and the view insert.
~Berend Tober
On Friday 19 September 2003 13:36, btober@seaworthysys.com wrote:
> > On Friday 19 September 2003 09:00, btober@seaworthysys.com wrote:
> >> I'm finding that column defaults are not being assigned to nulls when
> >> I do an insert by way of a an ON INSERT rule on a view. For example,
> >> the following script
> >
> > [snip]
> >
> > Hmm - well, you're explicitly telling it to insert VALUES (...,
> > new.field3, ...) so if new.field3 is null then it *should* do that.
>
> I (apparently mistakenly) thought that the point of specifying DEFAULT
> values in the table column definition was so that the default value would
> be inserted automatically rather than a null. And anyway, that IS how it
> seems to work when I do the insert for row A to the table directly with
>
> INSERT INTO test_table VALUES ('A');
>
> where fields 2, 3, and 4 have not been assigned values. Why do they get
> the default in this case?
Because you're not doing
INSERT INTO test_table VALUES ('A',null,null)
> > Now - how you should go about getting the default I don't know. You
> > could build a rule with WHERE NEW.field3 IS NULL and then not pass
> > field3, but that would stop you explicitly setting it to null.
>
> My work-around has been to define BEFORE INSERT triggers with lines like
>
> SELECT INTO new.field3 COALESCE(new.field3, 1);
>
> testing for and optionally assigning the default, but I really don't like
> having to explicitly do that for every table and NOT NULL column, since I
> make pretty much routine use of RULES on VIEWS to make writeable views
> the interface to my user application.
>
> > Out of curiosity, can you tell me what happens if you insert into the
> > view ('C',DEFAULT,DEFAULT)?
> > Richard Huxton
>
> Same script, but with
>
>
> INSERT INTO test_table VALUES ('A');
> INSERT INTO test_table_v VALUES ('B');
> INSERT INTO test_table_v VALUES ('C', DEFAULT, DEFAULT);
>
> gives
>
> field1 | field2 | field3 | field4
> --------+--------+--------+-----------------
> A | 1 | 1 | (default value)
> B | 2 | |
> C | 3 | |
> (3 rows)
>
> -- so no change in behavior. I notice that field2, which was declared
> type SERIAL, and so also has a DEFAULT, but one which calls the nextval
> function rather than simply assigning a value, gets its default value
> assigned in both the table insert and the view insert.
Because you don't specify field2 in your RULE.
--
Richard Huxton
Archonet Ltd
Richard Huxton <dev@archonet.com> writes:
> On Friday 19 September 2003 09:00, btober@seaworthysys.com wrote:
>> Is this supposed to work that way? I would expect field3 and field4 to
>> have their respective column defaults assigned on the second INSERT (row
>> B), just like on the first INSERT (row A).
> Hmm - well, you're explicitly telling it to insert VALUES (..., new.field3,
> ...) so if new.field3 is null then it *should* do that.
Exactly. The defaults attached to the underlying table determine what
gets added to an INSERT into the underlying table. In this case, since
the rule's INSERT specifies all the fields, there is no scope for those
defaults to apply.
What Berend actually wants is to attach column defaults to the *view*,
so that they apply to an INSERT mentioning the view. You can do this in
recent PG releases (7.3 for sure, not sure about 7.2) using ALTER TABLE
... ADD DEFAULT.
regards, tom lane
On Friday 19 September 2003 16:04, Tom Lane wrote: > Richard Huxton <dev@archonet.com> writes: > > On Friday 19 September 2003 09:00, btober@seaworthysys.com wrote: > >> Is this supposed to work that way? I would expect field3 and field4 to > >> have their respective column defaults assigned on the second INSERT (row > >> B), just like on the first INSERT (row A). > > > > Hmm - well, you're explicitly telling it to insert VALUES (..., > > new.field3, ...) so if new.field3 is null then it *should* do that. > > Exactly. The defaults attached to the underlying table determine what > gets added to an INSERT into the underlying table. In this case, since > the rule's INSERT specifies all the fields, there is no scope for those > defaults to apply. > > What Berend actually wants is to attach column defaults to the *view*, > so that they apply to an INSERT mentioning the view. You can do this in > recent PG releases (7.3 for sure, not sure about 7.2) using ALTER TABLE > ... ADD DEFAULT. Hmm - didn't know that. Useful. Am I right in thinking that *in theory* we should be able to "inherit" the defaults to the view automatically? -- Richard Huxton Archonet Ltd
> Richard Huxton <dev@archonet.com> writes: >> On Friday 19 September 2003 09:00, btober@seaworthysys.com wrote: >>> Is this supposed to work that way? I would expect field3 and field4 >>> to have their respective column defaults assigned on the second >>> INSERT (row B), just like on the first INSERT (row A). > >> Hmm - well, you're explicitly telling it to insert VALUES (..., >> new.field3, ...) so if new.field3 is null then it *should* do that. > > Exactly. The defaults attached to the underlying table determine what > gets added to an INSERT into the underlying table. In this case, since > the rule's INSERT specifies all the fields, there is no scope for those > defaults to apply. > > What Berend actually wants is to attach column defaults to the *view*, > so that they apply to an INSERT mentioning the view. You can do this > in recent PG releases (7.3 for sure, not sure about 7.2) using ALTER > TABLE ... ADD DEFAULT. Now THAT's WAY cool! I can do ALTER TABLE ...ADD DEFAULT against views? Sounds like exactly the ticket. Just tried it. It works. That is SO sweet...the more I learn about pg the more excited I get. ~Berend Tober
Richard Huxton <dev@archonet.com> writes:
> On Friday 19 September 2003 16:04, Tom Lane wrote:
>> What Berend actually wants is to attach column defaults to the *view*,
>> so that they apply to an INSERT mentioning the view. You can do this in
>> recent PG releases (7.3 for sure, not sure about 7.2) using ALTER TABLE
>> ... ADD DEFAULT.
> Am I right in thinking that *in theory* we should be able to "inherit" the
> defaults to the view automatically?
[shrug] Maybe, in simple cases. I'm not convinced it'd be especially
useful. The defaults applicable to direct inserts into a table aren't
necessarily sensible for inserts via a view.
regards, tom lane
> On Friday 19 September 2003 16:04, Tom Lane wrote: >> Richard Huxton <dev@archonet.com> writes: >> > On Friday 19 September 2003 09:00, btober@seaworthysys.com wrote: >> >> Is this supposed to work that way? I would expect field3 and >> field4 to have their respective column defaults assigned on the >> second INSERT (row B), just like on the first INSERT (row A). >> > >> > Hmm - well, you're explicitly telling it to insert VALUES (..., >> new.field3, ...) so if new.field3 is null then it *should* do that. >> >> Exactly. The defaults attached to the underlying table determine >> what gets added to an INSERT into the underlying table. In this >> case, since the rule's INSERT specifies all the fields, there is no >> scope for those defaults to apply. >> >> What Berend actually wants is to attach column defaults to the >> *view*, so that they apply to an INSERT mentioning the view. You can >> do this in recent PG releases (7.3 for sure, not sure about 7.2) >> using ALTER TABLE ... ADD DEFAULT. > > Hmm - didn't know that. Useful. > > Am I right in thinking that *in theory* we should be able to "inherit" > the defaults to the view automatically? I think that what you describe here as inheriting the defaults is the behavior I mistakenly expected as normal. But being able to assigne defaults to view columns is pretty darn cool. ~Berend Tober