Обсуждение: 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
>