CODEWITHSUNDEEP
postgresql
Sim
Sim

Reputation: 4184

How to implicitly insert SERIAL ID via view over more than one table

I have two tables, connected in E/R by a is-relation. One representing the "mother table"

CREATE TABLE PERSONS(
      id                SERIAL NOT NULL,
      name              character varying NOT NULL,
      address           character varying NOT NULL,
      day_of_creation   timestamp NOT NULL DEFAULT current_timestamp,
      PRIMARY KEY (id)
)

the other representing the "child table"

CREATE TABLE EMPLOYEES (
      id               integer NOT NULL,
      store            character varying NOT NULL,
      paychecksize     integer NOT NULL,
      FOREIGN KEY (id)
              REFERENCES PERSONS(id),
      PRIMARY KEY (id)
)

Now those two tables are joined in a view

CREATE VIEW EMPLOYEES_VIEW AS
    SELECT 
       P.id,name,address,store,paychecksize,day_of_creation
    FROM 
       PERSONS AS P
    JOIN
       EMPLOYEES AS E ON P.id = E.id

I want to write either a rule or a trigger to enable a db user to make an insert on that view, sparing him the nasty details of the splitted columns into different tables.

But I also want to make it convenient, as the id is a SERIAL and the day_of_creation has a default value there is no actual need that a user has to provide those, therefore a statement like

INSERT INTO EMPLOYEES_VIEW (name, address, store, paychecksize)
VALUES ("bob", "top secret", "drugstore", 42)

should be enough to result in

PERSONS

id|name|address   |day_of_creation
-------------------------------
1 |bob |top secret| 2013-08-13 15:32:42

EMPLOYEES

id|   store |paychecksize
---------------------
1 |drugstore|42

A basic rule would be easy as

CREATE RULE EMPLOYEE_VIEW_INSERT AS ON INSERT TO EMPLOYEE_VIEW
DO INSTED (
    INSERT INTO PERSONS
    VALUES (NEW.id,NEW.name,NEW.address,NEW.day_of_creation),
    INSERT INTO EMPLOYEES
    VALUES (NEW.id,NEW.store,NEW.paychecksize)
)

should be sufficient. But this will not be convenient as a user will have to provide the id and timestamp, even though it actually is not necessary.

How can I rewrite/extend that code base to match my criteria of convenience?

Upvotes: 1

Views: 158

Answers (1)

user330315
user330315

Reputation:

Something like:

CREATE RULE EMPLOYEE_VIEW_INSERT AS ON INSERT TO EMPLOYEES_VIEW
DO INSTEAD 
(
    INSERT INTO PERSONS (id, name, address, day_of_creation)
    VALUES (default,NEW.name,NEW.address,default);
    INSERT INTO EMPLOYEES (id, store, paychecksize)
    VALUES (currval('persons_id_seq'),NEW.store,NEW.paychecksize)
);

That way the default values for persons.id and persons.day_of_creation will be the default values. Another option would have been to simply remove those columns from the insert:

INSERT INTO PERSONS (name, address)
VALUES (NEW.name,NEW.address);

Once the rule is defined, the following insert should work:

insert into employees_view (name, address, store, paychecksize)
values ('Arthur Dent', 'Some Street', 'Some Store', 42);

Btw: with a current Postgres version an instead of trigger is the preferred way to make a view updateable.

Upvotes: 1

Related Questions