1

I have 3 tables: 'news', 'requirements', 'conditions'. News - basic table. requirements and conditions dependency from 'news' through the consolidated (news_mechanic). How to insert data into all tables at once with a single query. I know, i must use trigger. But, how to do it?

CREATE TABLE news (id_news SERIAL PRIMARY KEY, text_news VARCHAR NULL, date_created date NULL).
CREATE TABLE conditions (id, gender_men boolean NULL, gender_women boolean NULL, age_from INTEGER NULL, age_to INTEGER NULL, etc).
CREATE TABLE requirements (id, name VARCHAR, mechanic_test, mechanic_cunsultation, etc).
Павел Филимонов
  • 21
  • 4
  • And news_mechanic. `CREATE TABLE news_mechanic (id_news INTEGER, id_requirements INTEGER, id_conditions INTEGER)` – Павел Филимонов Mar 19 '19 at 13:10
  • There are many ways to do it ? such has have a procedure with a BEGIN TRANSACTION, or put a trigger on table After insert, Can you show what you have tried so far ? Thanks – Markov Mar 19 '19 at 13:12
  • If it should be all or nothing, it should be inserted in same transaction. There is no command to insert to 3 tables. Trigger is not mandatory, can be stored procedure or just INSERT statements – igr Mar 19 '19 at 13:13
  • @igr: yes you can insert into multiple tables using a single statement. –  Mar 19 '19 at 13:15

1 Answers1

0

Use CTEs:

with news_i as (
      insert into news( . . . )
          . . .
          returning (*)
     ),
     conditions_i as (
      insert into conditions ( . . . )
          . . . 
          returning (*)
     )
insert into requirements ( . . . )
    . . .;

Each CTE returns the values being inserted, so they can be used in later logic in the query. I don't see foreign key relationships in your data model, so I don't know if you need data from one table in the others.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786