I'm using 7.1.3 currently, but am building and installing 7.2.1 tonight
to see if this fixes the problem.
I don't know the standard types and functions well enough to be able to
whip out a test case, but I think I do have an idea on what the problem
is. If I'm right, the problem is triggered by any rule with a function
that operates on one of the parameters. If the parameter is already the
type then the rule succeeds. If the parameter needs to be cast (e.g.,
because it's a literal value) then the rule fails.
E.g., if there is a function like
function strlen(varchar) returns int4 ...
try
create table test (s varchar(20), len int4);
create view test_view as select s from test;
create rule test_rule as on insert to test_view
do instead insert into test (s, strlen(s));
then
insert into test_view values ('crash-n-burn!');
will fail.
Taken even further, you could probably use
create rule test_rule2 as on insert to test_view
do instead insert into test2 (strlen(s));
The earlier example is just an updateable view with the tweak that
some of hidden underlying fields are also updated. Strictly speaking
this breaks 3NF, but with the consistency checks it's a useful way of
caching derived values while ensuring that they can't get out of sync
with the objects they cache.
Bear
P.S., it just occured to me that rules can allow multiple statements.
Maybe the workaround is
create rule...
do instead (
insert into temporary table;
insert into final table from temporary table using functions;
clear temporary table
);
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
