PostgreSQL: Conditional INSERT INTO on some of multiple columns from difference tables

Please, I am a newbie in PostgreSQL/PLPGSQL and I have tried to search on SO here and there on Google, but could not find specific solutions. I do apologies if this question is duplicated.

I want to create trigger function(s) that can insert values from table_main and if other sub-tables have values, a custom string (link) will be inserted into the same row in line with data from table_main.

I have 3 tables which auto-generated by an application, which are similar to:

-- table_main

CREATE TABLE table_main
(
  main_a character varying(80) NOT NULL,
  main_b character varying(255),
  main_c character varying(255),
  main_d character varying(255),
  CONSTRAINT main_a_pkey UNIQUE (main_a)
);

-- table_sub1

CREATE TABLE table_sub1
(
  sub_a character varying(80) NOT NULL,
  sub_b character varying(255),
  sub_c character varying(255),
  sub_d character varying(255),
  CONSTRAINT table_sub1_pkey UNIQUE (sub_a)
);

-- table_sub2

CREATE TABLE table_sub2
(
  sub2_a character varying(80) NOT NULL,
  sub2_b character varying(255),
  sub2_c character varying(255),
  sub2_d character varying(255),
  CONSTRAINT table_sub2_pkey UNIQUE (sub2_a)
);

Also, I have another table to store data to be copied from table_main and custom strings (links), as below:

-- table_copy

CREATE TABLE table_copy
(
  id serial NOT NULL,
  copy_a character varying(80),
  copy_b character varying(255),
  copy_c character varying(255),
  copy_d character varying(255),
  copy_e character varying(255),
  copy_f character varying(255),
  copy_g character varying(255)
);

My purpose is to:

  1. Copy data from table_main to table_copy when a record is inserted into table_main, using below trigger function, and it works fine:

.

CREATE OR REPLACE FUNCTION save_data()
RETURNS trigger
AS $$
BEGIN
INSERT INTO table_copy (
copy_a,
copy_b,
copy_c,
copy_d)
VALUES ( 
NEW."main_a", 
NEW."main_b",
NEW."main_c",
NEW."main_d"
);
RETURN NEW;
END $$ LANGUAGE plpgsql;
ALTER FUNCTION save_data() OWNER TO postgres;
DROP TRIGGER trigger_save_data ON "table_main";
CREATE TRIGGER trigger_save_data BEFORE INSERT OR UPDATE 
ON "table_main" FOR EACH ROW EXECUTE PROCEDURE save_data();

Check and result:

smart=# INSERT INTO table_main (main_a, main_b, main_c, main_d) VALUES ('aaa', 'bbb', 'ccc', 'ddd');
INSERT 0 1
smart=# SELECT * FROM table_copy;
 id | copy_a | copy_b | copy_c | copy_d | copy_e | copy_f | copy_g
----+--------+--------+--------+--------+--------+--------+--------
  3 | aaa    | bbb    | ccc    | ddd    |        |        |
(1 row)
  1. My second (and more important) purpose is to insert custom strings into some last columns in table_copy (in the same row, of course) with conditions based on table_sub1 and table_sub2, e.g, IF table_sub1.sub_c or table_sub2.sub2_d have values inserted, the custom strings (links) to be inserted into table_copy.copy_e or table_copy.copy_f, respectively.

I have tried some solutions below but all do not satisfy me.

Solution 1: Set each trigger on each table of table_main, table_sub1 and table_sub2. Result: It creates three rows in table_copy ==> not accepted.

Trigger on table_sub1:

CREATE OR REPLACE FUNCTION save_data_sub1()
RETURNS trigger
AS $$
BEGIN
INSERT INTO table_copy (copy_e) VALUES ('link_to_sub1_here');
RETURN NEW;
END $$ LANGUAGE plpgsql;
ALTER FUNCTION save_data_sub1() OWNER TO postgres;
CREATE TRIGGER trigger_save_data_sub1 BEFORE INSERT OR UPDATE 
ON "table_sub1" FOR EACH ROW EXECUTE PROCEDURE save_data_sub1();

Trigger on table_sub2:

CREATE OR REPLACE FUNCTION save_data_sub2()
RETURNS trigger
AS $$
BEGIN
INSERT INTO table_copy (copy_f) VALUES ('link_to_sub2_here');
RETURN NEW;
END $$ LANGUAGE plpgsql;
ALTER FUNCTION save_data_sub2() OWNER TO postgres;
CREATE TRIGGER trigger_save_data_sub1 BEFORE INSERT OR UPDATE 
ON "table_sub2" FOR EACH ROW EXECUTE PROCEDURE save_data_sub2();

Test and results (with all 3 triggers above):

smart=# INSERT INTO table_sub1 VALUES ('abc', 'bca', 'aaa', 'cba');
INSERT 0 1
smart=# INSERT INTO table_sub2 VALUES ('cvb', 'bvc', 'aaa', 'vcb');
INSERT 0 1
smart=# SELECT * FROM table_copy;
 id | copy_a | copy_b | copy_c | copy_d |      copy_e       |      copy_f       | copy_g
----+--------+--------+--------+--------+-------------------+-------------------+--------
  6 | aaa    | bbb    | ccc    | ddd    |                   |                   |
  7 |        |        |        |        | link_to_sub1_here |                   |
  8 |        |        |        |        |                   | link_to_sub2_here |
(3 rows)


smart=#

Solution 2: Using IF EXISTS (SELECT........ WHERE) THEN INSERT INTO table_copy (copy_e) VALUES ('a link here') END IF; right below RETURN NEW; in the trigger function save_data() above, it is still resulted new rows. ==> not accepted also.

Solution 3: Using the same IF EXISTS ...... THEN .... above, ==> ERROR.

What I want is, the data is inserted into one row (6), not being generated to three rows. Here is my expectation:

 id | copy_a | copy_b | copy_c | copy_d |      copy_e       |      copy_f       | copy_g
----+--------+--------+--------+--------+-------------------+-------------------+--------
  6 | aaa    | bbb    | ccc    | ddd    | link_to_sub1_here | link_to_sub2_here |

As said above, I am newbie and could not find solutions to make my dream come true. Please, any expert here can give me a light.

Answers


Thank you all here for your suggestions and comments. Finally I got it work with below solution.

Since the table_main.main_a column is pkey of table_main, and there is one (not pkey or fkey) column in each table_sub1 and table_sub2 have the same value with table_main.main_a. In addition, values (if any) come to table_sub1 and table_sub2 first, then come to table_main (always). So, my trigger function below works, perfectly:

CREATE OR REPLACE FUNCTION save_data()
RETURNS trigger
AS $$
BEGIN
INSERT INTO table_copy (
copy_a,
copy_b,
copy_c,
copy_d)
VALUES ( 
NEW."main_a", 
NEW."main_b",
NEW."main_c",
NEW."main_d"
);
--Since value in table_sub1.sub_c is the same value in table_main.main_a
PERFORM sub_c FROM table_sub1 WHERE sub_c = NEW.main_a;
IF FOUND THEN UPDATE table_copy SET copy_e = ('link_to_sub1_here');
END IF;
--Since value in table_sub2.sub2_c is also the same value in table_main.main_a
PERFORM sub2_c FROM table_sub2 WHERE sub2_c = NEW.main_a;
IF FOUND THEN UPDATE table_copy SET copy_f = ('link_to_sub2_here');
END IF;
RETURN NEW;
END $$ LANGUAGE plpgsql;
ALTER FUNCTION save_data() OWNER TO postgres;
DROP TRIGGER trigger_save_data ON "table_main";
CREATE TRIGGER trigger_save_data BEFORE INSERT OR UPDATE 
ON "table_main" FOR EACH ROW EXECUTE PROCEDURE save_data();

And here is test result:

postgres=# INSERT INTO table_sub2 VALUES ('cdef', 'abv', 'abcde', 'a12');
INSERT 0 1
postgres=# INSERT INTO table_sub1 VALUES ('cdeb', 'abv', 'abcde', 'a12');
INSERT 0 1
postgres=# INSERT INTO table_main (main_a, main_b, main_c, main_d) VALUES ('abcde', 'bbb', 'ccc', 'ddd');
INSERT 0 1
postgres=# SELECT * FROM table_copy;
 id | copy_a | copy_b | copy_c | copy_d |      copy_e       |      copy_f       | copy_g
----+--------+--------+--------+--------+-------------------+-------------------+--------
  6 | abcde  | bbb    | ccc    | ddd    | link_to_sub1_here | link_to_sub2_here |
(1 row)


postgres=#

Just provide solution for someone needed.

Cheers.


Need Your Help

Use nth-child value as a SASS variable

css sass css-selectors

Is there any way to use the nth-child value as a SASS variable ?

Cannot CSS rotate inner div when resizing outer div

jquery css3 coffeescript rotation css-transforms

This one has me stumped for days. I have a simple setup. Outer DIV, Inner DIV, image inside inner DIV. I am trying to use CSS to rotate the inner div and then resize the outer div to the inner d...