gistfile1.sql

-- -*- sql-product: postgres; sqlind-minor-mode: -1; -*-

DROP SEQUENCE IF EXISTS plans_id_seq;
CREATE SEQUENCE plans_id_seq;

CREATE TABLE plans (
       id   BIGINT DEFAULT nextval('plans_id_seq') PRIMARY KEY,
       name TEXT NOT NULL
);

CREATE TABLE cobra_plans (
       id               BIGINT DEFAULT nextval('plans_id_seq'),
       tier_schedule_id BIGINT NOT NULL
) INHERITS (plans);

CREATE TABLE fsa_plans (
       id         BIGINT DEFAULT nextval('plans_id_seq'),
       sponsor_id BIGINT NOT NULL
) INHERITS (plans);

INSERT INTO cobra_plans (name, tier_schedule_id) VALUES ('Aetna Vision Premium', 20);
INSERT INTO fsa_plans (name, sponsor_id) VALUES ('TX0 FSA TU90', 120);

-- roman=# select * from plans;
--  id |         name
-- ----+----------------------
--   1 | Aetna Vision Premium
--   2 | TX0 FSA TU90
-- (2 rows)

-- roman=# select * from cobra_plans;
--  id |         name         | tier_schedule_id
-- ----+----------------------+------------------
--   1 | Aetna Vision Premium |               20
-- (1 row)

-- roman=# select * from fsa_plans;
--  id |     name     | sponsor_id
-- ----+--------------+------------
--   2 | TX0 FSA TU90 |        120
-- (1 row)

-- roman=# INSERT INTO cobra_plans (name, tier_schedule_id) VALUES ('BCBSIL PPO Blue Choice Premium 3500', 99);
-- INSERT 0 1
-- roman=# SELECT * FROM plans;
--  id |                name
-- ----+-------------------------------------
--   1 | Aetna Vision Premium
--   3 | BCBSIL PPO Blue Choice Premium 3500
--   2 | TX0 FSA TU90
-- (3 rows)

-- roman=# SELECT * FROM cobra_plans;
--  id |                name                 | tier_schedule_id
-- ----+-------------------------------------+------------------
--   1 | Aetna Vision Premium                |               20
--   3 | BCBSIL PPO Blue Choice Premium 3500 |               99
-- (2 rows)