okbob/plpgsql_check

false error with rule on table

klementszabolcs opened this issue · 3 comments

klementszabolcs commented

when check a function with query a table with insert/update rule, false error detected:
"error:XX000:qq:SQL statement:there is not single query"
qq-rownumber

okbob commented
klementszabolcs commented

CREATE TABLE IF NOT EXISTS xakta
(
aktaid integer NOT NULL,
CONSTRAINT pk_xakta PRIMARY KEY (aktaid)
);
CREATE or replace RULE xrule_akta2 AS ON INSERT TO xakta DO select 1;

CREATE OR REPLACE FUNCTION public.fff(p_aa integer)
RETURNS integer LANGUAGE plpgsql AS
$body$
Begin
insert into xakta values( 1 );
return 0;
End;
$body$ VOLATILE COST 100 SECURITY DEFINER;

select exts.plpgsql_check_function('fff');

error:XX000:3:SQL statement:there is not single query
Detail: plpgsql_check cannot detect result type
Hint: Probably there are some unsupported (by plpgsql_check) rules on related tables

okbob commented

It is hard to say what can be correct solution. Rules are bad, because you can modify INSERT into SELECT or do some crazy things, and it is hard to say, what plpgsql_check should to do. PLpgSQL ignore this and just takes an result of last rule, but sometimes this can be unwanted. Generally using "rules" is bad practice, and I don't know, what usage is correct and what not, what is expected and what not, and then I cannot to write support for rules to plpgsql_check. I afraid so I cannot to fix this issue - not technically, but I cannot to separate correct usage of rules from wrong usage of rules. The result is correct for plpgsql_check, because it is fatal error on input, and plpgsql_check cannot to correctly handle it. You can use pragmas to partially disable plpgsql_check.

Generally if it is possible, don't use rules - it is very old feature - designed ten years before plpgsql, and together it can do some crazy things. In this case I am internally against to follow plpgsql, that is not sensitive to this behave, because it is invisible for plpgsql.