Thread: 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