Parsing the result of a function to a view in an INSERT statement.
От | Donald Fraser |
---|---|
Тема | Parsing the result of a function to a view in an INSERT statement. |
Дата | |
Msg-id | 001a01c30a51$7366c6d0$1664a8c0@DEMOLITION обсуждение исходный текст |
Ответы |
Re: Parsing the result of a function to a view in an INSERT statement.
|
Список | pgsql-bugs |
I have observed that using a function to provide a default value on a colum= n of a view or attempting to parse the result of a function to a view in an= insert or update statement produces undesirable results. For example if I have a column named "id" where ever I have made reference = to NEW.id in the INSERT or UPDATE rules of the view it appears to be replac= ed with the function call rather than the result of the function call. See = the example code and output below. I don't know whether this is the default behaviour and one is expected to p= rogram around this behaviour or whether this is in fact undesirable behavio= ur and therefore should be considered as a bug? Regards Donald Fraser. Example SQL Code: CREATE OR REPLACE FUNCTION public.notice_id(text, int4) RETURNS void AS '= =20 DECLARE=20 smsge ALIAS FOR $1; id ALIAS FOR $2; BEGIN=20 RAISE NOTICE ''%, id is: %'', smsge, id; RETURN VOID; END;' LANGUAGE 'plpgsql' STABLE SECURITY DEFINER; CREATE TABLE tbl_test1 ( id int4 NOT NULL, id_test2 int4, CONSTRAINT tbl_te= st1_pkey PRIMARY KEY (id) ) WITHOUT OIDS; CREATE OR REPLACE RULE rul_tbl_test1_i01 AS ON INSERT TO tbl_test1 DO (SELE= CT notice_id('id during insert', NEW.id)); CREATE SEQUENCE tbl_test_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 4294967295 = CACHE 1; GRANT ALL ON tbl_test_id_seq TO PUBLIC; CREATE OR REPLACE VIEW vu_tbl_test1 AS SELECT id, id_test2 FROM tbl_test1; GRANT SELECT, INSERT, UPDATE ON TABLE vu_tbl_test1 TO PUBLIC; ALTER TABLE vu_tbl_test1 ALTER COLUMN id SET DEFAULT nextval('tbl_test_id_s= eq'::text); CREATE OR REPLACE RULE rul_vu_tbl_test1_01 AS ON INSERT TO vu_tbl_test1 DO = (SELECT notice_id('id before insert', NEW.id)); CREATE OR REPLACE RULE rul_vu_tbl_test1_02 AS ON INSERT TO vu_tbl_test1 DO = INSTEAD (INSERT INTO tbl_test1(id, id_test2) VALUES (NEW.id, NEW.id_test2)); CREATE OR REPLACE RULE rul_vu_tbl_test1_03 AS ON INSERT TO vu_tbl_test1 DO = (SELECT notice_id('id after insert', NEW.id)); Observed output: Bugs=3D> INSERT INTO vu_tbl_test1 (id_test2) VALUES('2'); NOTICE: id before insert, id is: 1 NOTICE: id during insert, id is: 3 NOTICE: id after insert, id is: 4 Bugs=3D> INSERT INTO vu_tbl_test1 (id,id_test2) VALUES(nextval('tbl_test_id= _seq'::text),'2'); NOTICE: id before insert, id is: 5 NOTICE: id during insert, id is: 7 NOTICE: id after insert, id is: 8
В списке pgsql-bugs по дате отправления: