Thread: default value dosen't get applyed in this situation
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);
(
CONSTRAINT tab_s1_check CHECK (1 = 1)
)
INHERITS (tab);
CREATE OR REPLACE VIEW v_tab AS
SELECT tab._id,
tab._name
FROM tab;
SELECT tab._id,
tab._name
FROM tab;
CREATE OR REPLACE FUNCTION tab_insert()
RETURNS trigger AS
$BODY$
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO tab_s1 VALUES ((NEW).*);
INSERT INTO tab_s1 VALUES ((NEW).*);
RETURN NEW;
END $BODY$
LANGUAGE plpgsql;
END $BODY$
LANGUAGE plpgsql;
CREATE TRIGGER tab_trigger
INSTEAD OF INSERT ON v_tab
FOR EACH ROW EXECUTE PROCEDURE tab_insert();
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');
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