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

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

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 key field
included into view.
2.) primary key fields can't be updated directly from view.
3.) primary key for each table must be a 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