Tackling nested inserts using functions

Question

Hi people i need some help deciding on the best way to do an insert into table ‘shop’ which has a serial id field. I also need to insert into tables ‘shopbranch’ and ‘shopproperties’ which both references shop.id.

In a nutshell I need to insert one shop record. Then two records for each table of the following tables, shopproperty and shopbranch, whose shopid (FK) references the just created shop.id field

I saw somewhere that i could wrap the ‘shop’ insert, inside a function called lets say ‘insert_shop’ which does the 'shop' insert and returns its id using a select statement

Then inside another function which inserts shoproperty and shopbranch records i could do one call to insert_shop function to return the shop id which can be used to be passed in as the shop id for the records.

Can you let me know if I’m looking at this in the correct way as I’m a newbie.

In SQL-Server this is usually handled with `INSERT` triggers. — , Mar 12 '14 at 13:22
Im working with Postgres 9.3. Can you give me an overview in the strategy of how you would do this as i need to create a script to import some test data in a Postgres database. would be really appreciated — L-Samuels, Mar 12 '14 at 13:39
Here is documentation on PostgreSQL Triggers http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html Looks close enough to SQL server, so approach should be the same. Create `after trigger` on main table, and use the value that was added to insert records into children tables. — , Mar 12 '14 at 13:58
Also look at this question http://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id with that you can get ID of what was inserted and use that to insert other records. You can wrap that inside Procedure or Function. — , Mar 12 '14 at 14:02

score 1 · Accepted Answer · answered Mar 15 '14 at 06:21

One way to approach this is to create a view on your three tables that shows all columns from all three tables that can be inserted or updated. If you then create an INSTEAD OF INSERT trigger on the view then you can manipulate the view contents as if it were a table. You can do the same with UPDATE and even combine the two into an INSTEAD OF INSERT OR UPDATE trigger. The function that your trigger calls then has three INSERT statements that redirect the insert on the view to the underlying tables:

CREATE TABLE shop (
  id serial PRIMARY KEY,
  nm text,
  ...
);

CREATE TABLE shopbranch (
  id serial PRIMARY KEY,
  shop integer NOT NULL REFERENCES shop,
  branchcode text,
  loc text,
  ...
);

CREATE TABLE shopproperties (
  id serial PRIMARY KEY,
  shop integer NOT NULL REFERENCES shop,
  prop1 text,
  prop2 text,
  ...
);

CREATE VIEW shopdetails AS
  SELECT s.*, b.*, p.*
  FROM shop s, shopbranch b, shopproperties p,
  WHERE b.shop = s.id AND p.shop = s.id;

CREATE FUNCTION shopdetails_insert() RETURNS trigger AS $$
DECLARE
  shopid integer;
BEGIN
  INSERT INTO shop (nm, ...) VALUES (NEW.nm, ...) RETURNING id INTO shopid;
  IF NOT FOUND
    RETURN NULL;
  END;
  INSERT INTO shopbranch (shop, branchcode, loc, ...) VALUES (shopid, NEW.branchcode, NEW.loc, ...);
  INSERT INTO shopproperties(shop, prop1, prop2, ...) VALUES (shopid, NEW.prop1, NEW.prop2, ...);
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER shopdetails_trigger_insert
  INSTEAD OF INSERT
  FOR EACH ROW EXECUTE PROCEDURE shopdetails_insert();

You could of course play with the view and show only those columns from the three tables that can be inserted or updated (such as excluding primary and foreign keys).

Tackling nested inserts using functions

1 Answers1