0

I have quite a few many-to-many relationships. To simplify the process of inserting data into the respective three tables, I have the below function that I adapt for the various M:M relationships, and it works like a charm. However, for situations when dealing with many new records, I would like to simplify the insert process even further.

At the moment I am using an .xls sheet with columns (and their order of sequence) corresponding to how they are written in the function (ex. surname, fname, email, phone, docutype, year, title, citation, digital, url, call_number, report_no, docu_description)

I then import that .xls incl. data to a new table in the database, and using Navicat's 'Copy as insert statement', and further copy & replacing the function-call to the statement, I end up with function call statements for all records in the table looking similar to this:

SELECT junction_insert_into_author_reportav ('Smith', 'Victoria', 
some@email.com, NULL, 'Report',  '2010', ' Geographical Place Names', 
'Some citation, 'f', 'NULL', 'REP/63', NULL, NULL);

This works okay but I would like to reduce the steps involved even further if possible. For example by being able to pass the newly created table that I imported the .xls sheet into, as a parameter to the function -and then deleting the new table again after the insert statements in the function has run. I am just unsure how to do this, and if at all it is possible?

Here is an example of the function as it looks and works at the moment:

CREATE OR REPLACE FUNCTION junction_insert_into_author_reportav (
        p_surname VARCHAR,
        p_fname VARCHAR,
        p_email VARCHAR,
        p_phone TEXT,
        p_docutype VARCHAR,
        p_year int4,
        p_title VARCHAR,
        p_citation VARCHAR,
        p_digital bool,
        p_url TEXT,
        p_call_no VARCHAR,
        p_report_no VARCHAR,
        p_docu_description VARCHAR 
    ) RETURNS void AS $BODY$ 


DECLARE

    v_authorId INT;
    v_reportavId INT;

BEGIN
    SELECT
        author_id INTO v_authorId 
    FROM
        author 
    WHERE
        surname = p_surname 
        AND fname = p_fname;

    SELECT
        reportav_id INTO v_reportavId 
    FROM
        report_av 
    WHERE
        title = p_title;

    IF
        ( v_authorId IS NULL ) THEN
            INSERT INTO author ( surname, fname, email, phone )
        VALUES
            ( p_surname, p_fname, p_email, p_phone ) RETURNING author_id INTO v_authorId;

    END IF;

    IF
        ( v_reportavId IS NULL ) THEN
            INSERT INTO report_av ( docu_type, YEAR, title, citation, digital, url, call_number, report_no, docu_description )
        VALUES
            ( p_docutype, p_year, p_title, p_citation, p_digital, p_url, p_call_no, p_report_no, p_docu_description ) RETURNING reportav_id INTO v_reportavId;

    END IF;

    INSERT INTO jnc_author_reportav
    VALUES ( v_authorId, v_reportavId );

END;
$BODY$ LANGUAGE plpgsql VOLATILE COST 100
Ric
  • 75
  • 1
  • 8
  • May you could try to create an updatable view and just insert into that. – sticky bit Dec 25 '18 at 17:09
  • Thanks for the tip. Views could maybe be a solution. I would still like to have some automation on inserts into the junction table though. I will have to play a little more around with it. I am a little new to database development. Do you know how people generally approach streamlining inserts into M:M table relationships to make it more user friendly? Ex. are views the main approach, and/or what other approaches are there? – Ric Dec 26 '18 at 09:34
  • Hmm, I personally was happy with multiple `INSERT`s in the right order so far. But if I ever feel the urge to change that, updatable views would be a hot candidate, yes. Of course they have limitations. And so do procedures. But don't get me wrong, procedures aren't a wrong approach, not at all. People use them indeed for such problems. I just threw the views in as a suggestion, you possibly haven't though about yet but which you may like. But at the end of the day I guess that's all "primarily opinion-based". – sticky bit Dec 26 '18 at 09:48
  • Thanks, I only have myself to discuss these things with, so this kind of information is really helpful for my reflections – Ric Dec 26 '18 at 10:52

0 Answers0