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

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

"Donald Fraser" <demolish@cwgsy.net> writes:
> 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.

Rules are macros and as such you have to think about multiple
evaluations of their arguments.  You would probably be better off using
triggers for what you seem to be trying to accomplish.

            regards, tom lane

----- Original Message -----
>> "Donald Fraser" <demolish@cwgsy.net> writes:
>> 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.
>
> Rules are macros and as such you have to think about multiple
> evaluations of their arguments.  You would probably be better off using
> triggers for what you seem to be trying to accomplish.
>
> regards, tom lane

Sorry if my example, which used a rule on a table, was misleading in any way.
It was just part of the overall example and not an insight as to what I am
trying to achieve.

The problem is the evaluation of a function is not happening at the point where
it is first declared. Rather its definition is being passed on to the rule
re-write system and it becomes evaluated at each point where it is referenced
in the rule query tree.

For example: INSERT INTO myview (id, data1, data2) VALUES(get_id('1'),
'2','3');
The result of function get_id('1') is not evaluated first and passed on to the
rules of the view as one would expect. Rather wherever the queries in the rule
system references NEW.id, they appear to be re-written with get_id('1') before
they are run (ok this might not be exactly true but the results show something
to the effect of this). This can lead to strange results when the function
returns a different result each time it is evaluated and you did not anticipate
the user passing such a query that includes the result of such a function.
There is nothing to say that you cannot pass a function to a view, after all a
view is supposed to assimilate a table.

There are obviously ways to work around this obscurity, but it can be difficult
when you don't know how somebody will use a view in advance. The only sure way
that I can see is to combine all the rules into one function and have only one
rule that calls that function.

regards Donald Fraser.