I recently discovered a problem inserting a user-defined type when going through a rule. I'm not sure if it's a -hackers or -users question, but since it involves the interaction of a user-defined type and rules I thought it envitable that I would end up here anyway.
The object in question is my X.509 type. For compelling reasons beyond the scope of this discussion, I need to define a table as: create table certs ( name varchar(20), cert x509, -- fields used with CRL lookups serial_number hugeint not null constraint c1 check (serial_number = serial_number(cert)), issuer principal not null constraint c2 check (issuer = issuer(cert)), subject principal not null unique constraint c3 check (subject = subject(cert)), ... ); where the constraints guarantee that the cached attributes accurately reflect the contents of the cert (but these fields can be indexed and searched). In practice it's impossible to get those fields right in a query so I also defined: create view cert_insert as select name, cert from certs; create rule certi as on insert to cert_insert do instead insert into certs (name, cert, serial_number, subject, issuer,... ) values (new.name, new.cert, serial_number(new.cert), subject(new.cert), issuer(new.cert),... ); The problem is that I can insert literal text: create table t ( cert x509 ); insert into t values ('---- BEGIN CERTIFICATE ---- ....'); but when I try the same with cert_insert it's clear that "new.cert" isn't getting initialized properly. (It works fine when the cert is already in the database.) Trying to explicitly cast the literal to as part of the query doesn't help - it seems that the rule just rewrites the query and the cast is getting lost. Workarounds don't seem to be viable. I can't use a trigger on a temporary table since there doesn't seem to be a clean way to trigger a rule from one. (I need to get parameters from the trigger to the SQL function to the rule, and SQL functions don't seem to be able to take parameters -- or its undocumented if it can take something like $1, $2, etc.) I can't use a rule on the temporary table since it appears a rule still looks at the original parameters, not the temp table. Any ideas? Is this something addressed in 7.2? (I'm trying to stick with the oldest useable version to avoid forcing DB upgrades.) Or is this a genuine hole in the user type/rules/triggers model? Bear ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])