Insert Primary Key for every Foreign Key inserted

Question

In below tables, films table is a sub class of shows table. For every new row inserted (via web interface) into table shows I want to insert a row into films: For example,

INSERT INTO shows title VALUES ('title');

This will add a new row in shows with showid = next value in sequence, and title = 'title'... What I want is to get showid value (from shows) and insert it into new row in films table.

How can I do that?

CREATE SEQUENCE shows_showid_seq;
CREATE TABLE "shows" (
    "showid" BIGINT NOT NULL PRIMARY KEY DEFAULT nextval('shows_showid_seq'),
    "title" TEXT NOT NULL,
    "rating" BIGINT,
    "language" TEXT,
    "genre" TEXT
    );

CREATE TABLE "films" (
    "showid" BIGINT NOT NULL CONSTRAINT films_showid
    REFERENCES shows("showid") ON DELETE SET NULL,
    "year" INT, "reldate" DATE
    );

So you want to automate the insertion of records into films, when a row is inserted into films? — Scot Matson, Aug 09 '17 at 23:51
Yes, Whenever a new row inserted into table shows, and has showid = $value, I want insert a new row in films has showid = $value — hythm, Aug 09 '17 at 23:54

score 0 · Answer 1 · answered Aug 09 '17 at 23:58

0

So if I understand your question correctly, you are looking to simply automate an insertion on films after an insertion on shows has taken place.

So what you'll need to do first is write a FUNCTION to handle the insert operation (Reference: https://www.postgresql.org/docs/9.1/static/sql-createfunction.html)

And then write a TRIGGER which executes the function when a given event occurs (Reference: https://www.postgresql.org/docs/9.1/static/sql-createtrigger.html)

Reference to another post that has an example of a function, How to insert data into table using stored procedures in postgresql

And another SO reference that is a bit more comprehensive and includes a TRIGGER along with a FUNCTION, Postgresql: Creating a Trigger.

This should be enough to help you stitch something together. About to step out of the house, otherwise I would play around and write this out, I could certainly use the practice. But I hope this is enough to help move you in the right direction.

answered Aug 09 '17 at 23:58

Scot Matson

745
6
23

1

Thank you for the links, I'm reading through them now. I also updated my question hopefully make it more clear. – hythm Aug 10 '17 at 00:08
1

So after reviewing docs I created a function (don't think I need TRIGGER in such case): `CREATE OR REPLACE FUNCTION add_movie(mtitle TEXT) RETURNS VOID AS' DECLARE curshowid BIGINT; BEGIN INSERT INTO SHOWS (title) VALUES (mtitle) RETURNING showid INTO curshowid; INSERT INTO films (showid) VALUES (curshowid); END; ' LANGUAGE plpgsql;` and to call the function: SELECT add_movie('New Movie!'); So that was my solution, I would like to see if there are better solution to do same thing before I mark my thread as solved. – hythm Aug 10 '17 at 18:25
Nice work! I'm glad you were able to get it taken care of. SQL gets really interesting when you starting messing around with TRIGGERS and FUNCTIONS. I had only started using them a few months back myself. The only possible downside is if others are interacting with the database that are unaware of the setup they may not fully understand the magic happening behind the scenes. – Scot Matson Aug 10 '17 at 18:45

score 0 · Answer 2 · answered Aug 10 '17 at 20:44

Here is what I ended up doing:

CREATE FUNCTION add_movie(movie_title TEXT, movie_overview TEXT) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
current_showid BIGINT;
BEGIN
INSERT INTO shows (title, overview) VALUES (movie_title, movie_overview) RETURNING showid INTO current_showid;
INSERT INTO films (showid) VALUES (current_showid);
END;
$$;

And to insert a new movie I run: SELECT add_movie('Title', 'Overview');

Insert Primary Key for every Foreign Key inserted

2 Answers2