gistfile1.sql

-- DDL
-- company
CREATE TABLE company (
  id SERIAL PRIMARY KEY NOT NULL,
  name VARCHAR(64) NOT NULL,
  date_certif_start DATE NOT NULL,
  date_certif_end DATE NOT NULL,
  status SMALLINT NOT NULL,
  about VARCHAR NULL
);

-- address		
CREATE TABLE address (
  id SERIAL PRIMARY KEY,
  street VARCHAR(255) NOT NULL,
  additional VARCHAR(255) NULL,
  zip_code INT NOT NULL,
  city VARCHAR(64) NOT NULL,
  country_code INTEGER NOT NULL
);

CREATE TABLE company_address (
  id_company INTEGER,
  id_address INTEGER,
  PRIMARY KEY (id_company, id_address),
  CONSTRAINT fk_company_address__address FOREIGN KEY (id_address) REFERENCES address (id),
  CONSTRAINT fk_company_address__company FOREIGN KEY (id_company) REFERENCES company (id)
);

-- rule
CREATE TABLE rule (
  id SERIAL PRIMARY KEY,
  type VARCHAR(32) NOT NULL,
  description VARCHAR(255) NULL
);

CREATE TABLE company_rule (
  id_company INTEGER,
  id_rule INTEGER,
  PRIMARY KEY (id_rule, id_company),
  CONSTRAINT fk_company_rule__rule FOREIGN KEY (id_rule) REFERENCES rule (id),
  CONSTRAINT fk_company_rule__company FOREIGN KEY (id_company) REFERENCES company (id)
);

-- insertion
WITH 
company_store AS (
	INSERT INTO "company" 
		(name, date_certif_start, date_certif_end, status, about)
	VALUES
		('test4', '2014-03-22', '2016-03-22', 0, 'some text...')
	RETURNING id
	),
address_store AS (
	INSERT INTO "address" 
		(street, zip_code, city, country_code)
	VALUES
		('rue des petits anges', 75000, 'Paris', 33)
	RETURNING id
)
	
INSERT INTO company_rule ( id_company, id_rule )
	( SELECT id, '17'
	FROM company_store )
	
INSERT INTO company_address (id_company, id_address)
	( SELECT address_store.id, company_store.id
	FROM address_store, company_store );