Re: inserting user defined types through a rule?

Bear Giles <bgiles(at)coyotesong(dot)com> writes:
> 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.

I tried this, but apparently there's more to it than that; AFAICT it
works in the cases where I'd expect it to work (viz, where there is a
suitable cast function available).

test71=# create function strlen(varchar) returns int as
test71-# 'select length($1)::int' language 'sql';
CREATE
test71=# create table test (s varchar(20), len int4);
CREATE
test71=# create view test_view as select s from test;
CREATE
test71=# create rule test_rule as on insert to test_view
test71-# do instead insert into test values (new.s, strlen(new.s));
CREATE
test71=# insert into test_view values ('crash-n-burn!');
INSERT 1610948 1
test71=# insert into test_view values (33::int);
INSERT 1610949 1
test71=# insert into test_view values (33::numeric);
ERROR: Attribute 's' is of type 'varchar' but expression is of type 'numeric'
You will need to rewrite or cast the expression
test71=# select * from test;
s | len
---------------+-----
crash-n-burn! | 13
33 | 2
(2 rows)

Perhaps there's a particular case where it fails, but you'll have to
give us more of a clue...

regards, tom lane