Parsing the result of a function to a view in an INSERT statement.

From: "Donald Fraser" <demolish(at)cwgsy(dot)net>
To: "[BUGS]" <pgsql-bugs(at)postgresql(dot)org>
Subject: Parsing the result of a function to a view in an INSERT statement.
Date: 2003-04-24 11:05:38
Message-ID: 001a01c30a51$7366c6d0$1664a8c0@DEMOLITION
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I have observed that using a function to provide a default value on a column 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 replaced 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 program around this behaviour or whether this is in fact undesirable behaviour 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 '
DECLARE
smsge ALIAS FOR $1;
id ALIAS FOR $2;
BEGIN
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_test1_pkey PRIMARY KEY (id) ) WITHOUT OIDS;
CREATE OR REPLACE RULE rul_tbl_test1_i01 AS ON INSERT TO tbl_test1 DO (SELECT 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_seq'::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=> 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=> 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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2003-04-24 12:58:36 Re: Parsing the result of a function to a view in an INSERT statement.
Previous Message Paul Sundling("Webdaddy") 2003-04-24 10:31:11 pg_restore hangs on locale