Thread: default value dosen't get applyed in this situation

default value dosen't get applyed in this situation

From
Aliouii Ali
Date:
this a test case :
 

CREATE TABLE tab
(
  _id bigserial NOT NULL,
  _name text,
  CONSTRAINT tab_pkey PRIMARY KEY (_id)
);
CREATE TABLE tab_s1
(
CONSTRAINT tab_s1_check CHECK (1 = 1)
)
INHERITS (tab);
CREATE OR REPLACE VIEW v_tab AS
 SELECT tab._id,
    tab._name
   FROM tab;
CREATE OR REPLACE FUNCTION tab_insert()
 
RETURNS trigger AS
 $BODY$
BEGIN
    INSERT INTO tab_s1 VALUES ((NEW).*);
RETURN NEW;
END $BODY$
 
LANGUAGE plpgsql;
CREATE TRIGGER tab_trigger
    INSTEAD OF INSERT ON v_tab
    FOR EACH ROW EXECUTE PROCEDURE tab_insert();

-- the query fail because _id is null
insert into v_tab(_name) values ('uuuu');

Re: default value dosen't get applyed in this situation

From
Tom Lane
Date:
Aliouii Ali <aliouii.ali@aol.fr> writes:
> this a test case :

This is not supposed to insert a default.  Attach a default expression
to the view column, if you want inserts on the view to have defaults.
ALTER VIEW ... ALTER COLUMN ... SET DEFAULT is the way.

(Note that in recent versions of PG, that view would be auto-insertable
so you would not need to bother with the trigger.  But the situation
with defaults hasn't changed, and won't because it would be a backwards
compatibility break with no real value-add.)
        regards, tom lane