Skip site navigation (1) Skip section navigation (2)

Re: INSERT rule

From: "Darko Prenosil" <Darko(dot)Prenosil(at)finteh(dot)hr>
To: <pgsql-hackers(at)postgresql(dot)org>,"Jonathan Gardner" <jgardner(at)jonathangardner(dot)net>
Subject: Re: INSERT rule
Date: 2004-06-29 19:04:26
Message-ID: 00a301c45e0c$05e74e80$1e82bfd5@darko (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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(at)jonathangardner(dot)net>
To: "Darko Prenosil" <darko(dot)prenosil(at)finteh(dot)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
> > 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
> it for materialized views.
> --
> Jonathan Gardner
> jgardner(at)jonathangardner(dot)net

In response to

  • INSERT rule at 2004-06-29 10:51:19 from Darko Prenosil

pgsql-hackers by date

Next:From: Cason, KennyDate: 2004-06-29 19:07:30
Subject: Accessing Specific Schemas
Previous:From: Darko PrenosilDate: 2004-06-29 18:11:08
Subject: Re: User Privileges using dblink

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group