Thread: INSERT rule
Just wrote a function that takes view name as argument and generates INSERT, UPDATE and DELETE rules for that view. It is working OK (thanks to Tom), but I have trouble with INSERT rule. When inserting directly into table, default values are filled in when the field is not in the insert target list, but when using rule system field is filled with NULL. I understand that rule system rewrites the query as in CREATE RULE expression, but can I somehow detect actual attributes that are inserted, and avoid forced NULL inserts ? Tom said that current CVS support passing RECORD as an argument into the function. Is it good Idea to generate generic RULES that are simply passing NEW and OLD into some function, and try to solve updates inside that generic function ( instead of enumerating fields directly in CREATE RULE expression) ? Any suggestions ? (Sorry for bad English) Regards !
Darko Prenosil <darko.prenosil@finteh.hr> writes:
> Just wrote a function that takes view name as argument and generates INSERT,
> UPDATE and DELETE rules for that view. It is working OK (thanks to Tom), but
> I have trouble with INSERT rule.
> When inserting directly into table, default values are filled in when the
> field is not in the insert target list, but when using rule system field is
> filled with NULL.
You want to attach the defaults directly to the view, vizALTER TABLE view ALTER COLUMN col SET DEFAULT whatever;
regards, tom lane
God, that was so obvious ! Thanks (again). Regards ! ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Darko Prenosil" <darko.prenosil@finteh.hr> Cc: <pgsql-hackers@postgresql.org> Sent: Tuesday, June 29, 2004 4:18 PM Subject: Re: [HACKERS] INSERT rule > Darko Prenosil <darko.prenosil@finteh.hr> writes: > > Just wrote a function that takes view name as argument and generates INSERT, > > UPDATE and DELETE rules for that view. It is working OK (thanks to Tom), but > > I have trouble with INSERT rule. > > When inserting directly into table, default values are filled in when the > > field is not in the insert target list, but when using rule system field is > > filled with NULL. > > You want to attach the defaults directly to the view, viz > ALTER TABLE view ALTER COLUMN col SET DEFAULT whatever; > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match >
I do not have it here(at home), but I can send it tomorrow from work. This is first implementation and have some restrictions: 1.) all tables that are updated from view must have primary keyfield included into view. 2.) primary key fields can't be updated directly from view. 3.) primary key for each table must bea single field key This is because it is only way to build WHERE clause (the only way I know). Function generates rules for all fields it can update, and the rest is ignored (for example fields that are results of some function, or fields from table that has no primary key field included into view). I do not know a good way to avoid those restrictions :-( It requires some more work (one part is written using pl/pgsql, but I would like to rewrite it in C) According to Tom, 7.5 can pass record reference into function, and this opens some more possibilities. Maybe it would be even possible to write single generic function that can update any view. Regards ! ----- Original Message ----- From: "Jonathan Gardner" <jgardner@jonathangardner.net> To: "Darko Prenosil" <darko.prenosil@finteh.hr> Sent: Tuesday, June 29, 2004 8:22 PM Subject: Re: [HACKERS] INSERT rule > On Tuesday 29 June 2004 03:51 am, Darko Prenosil wrote: > > Just wrote a function that takes view name as argument and generates > > INSERT, UPDATE and DELETE rules for that view. It is working OK (thanks > > to Tom), but I have trouble with INSERT rule. > > When inserting directly into table, default values are filled in when the > > field is not in the insert target list, but when using rule system field > > is filled with NULL. I understand that rule system rewrites the query as > > in CREATE RULE expression, but can I somehow detect actual attributes > > that are inserted, and avoid forced NULL inserts ? > > Tom said that current CVS support passing RECORD as an argument into the > > function. Is it good Idea to generate generic RULES that are simply > > passing NEW and OLD into some function, and try to solve updates inside > > that generic function ( instead of enumerating fields directly in CREATE > > RULE expression) ? > > > > I am very interested in your code. Where can I find it? I would like to use > it for materialized views. > > -- > Jonathan Gardner > jgardner@jonathangardner.net >