inserting via "on insert" rule
Hi,
I have a table, say "person", where I store all the information of the
people I have in my files. I have a subgroup of people, say "users",
which I want to store on the same table "persons" by imposing some
restricitons on some columns.
I was thinking of defining a view "users" over "persons" which would let
me retrive the list of useres. But How would I implement the rule for
insertiung users? I tryed the following but NEW is not known where I
want to use it:
CREATE VIEW users AS
SELECT * FROM persons WHERE is_user(person.id) = TRUE;
CREATE RULE insert_on_users AS ON INSERT
TO users DO INSTEAD
INSERT INTO persons SELECT * FROM NEW;
The other thing I don't know is how to enforce the aditional constraints
that qualify as user when inserting to users. Of course I could do it on
the application side, but I would like to have it on the database too.
Any suggestions will be appreciated.
Regards,
Andreas Fromm
Andreas Fromm writes:
I was thinking of defining a view "users" over "persons" which would let
me retrive the list of useres. But How would I implement the rule for
insertiung users? I tryed the following but NEW is not known where I
want to use it:CREATE VIEW users AS
SELECT * FROM persons WHERE is_user(person.id) = TRUE;CREATE RULE insert_on_users AS ON INSERT
TO users DO INSTEAD
INSERT INTO persons SELECT * FROM NEW;
You can write
... DO INSTEAD INSERT INTO persons VALUES (NEW.col1, NEW.col2, ...);
The other thing I don't know is how to enforce the aditional constraints
that qualify as user when inserting to users. Of course I could do it on
the application side, but I would like to have it on the database too.
CREATE RULE insert_on_users AS ON INSERT TO users
WHERE is_user(NEW.id)
DO ...
Read the chapter on rules in the documentation to learn more about this.
--
Peter Eisentraut peter_e@gmx.net
Peter Eisentraut wrote:
Andreas Fromm writes:
I was thinking of defining a view "users" over "persons" which would let
me retrive the list of useres. But How would I implement the rule for
insertiung users? I tryed the following but NEW is not known where I
want to use it:CREATE VIEW users AS
SELECT * FROM persons WHERE is_user(person.id) = TRUE;CREATE RULE insert_on_users AS ON INSERT
TO users DO INSTEAD
INSERT INTO persons SELECT * FROM NEW;You can write
... DO INSTEAD INSERT INTO persons VALUES (NEW.col1, NEW.col2, ...);
Yes, but what if I don't pass a certain col. Is the default value
inserted instead? Or will the transaction fail because of a wrong number
of columns?
Peter Eisentraut wrote:
Andreas Fromm writes:
I was thinking of defining a view "users" over "persons" which would let
me retrive the list of useres. But How would I implement the rule for
insertiung users? I tryed the following but NEW is not known where I
want to use it:CREATE VIEW users AS
SELECT * FROM persons WHERE is_user(person.id) = TRUE;CREATE RULE insert_on_users AS ON INSERT
TO users DO INSTEAD
INSERT INTO persons SELECT * FROM NEW;You can write
... DO INSTEAD INSERT INTO persons VALUES (NEW.col1, NEW.col2, ...);
Yes, but the advantage of the select would be that I could do a SELECT
.. FROM .. WHERE , or how can I perform a checking of the data before
insertion?
Andreas