pginit.sql

CREATE TABLE forms (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE form_data(
    id SERIAL PRIMARY KEY,
    form_id INTEGER REFERENCES forms(id),
    data JSONB NOT NULL
);

INSERT INTO forms (name) VALUES 
    ('user_form'),
    ('survey_form'),
    ('order_form');

INSERT INTO form_data (form_id, data) VALUES 
    (1, '{"username": "alice", "email": "alice@example.com", "age": 30}'),
    (1, '{"username": "bob", "email": "bob@example.com", "age": 25}'),
    (1, '{"username": "charlie", "email": "charlie@example.com", "age": 35}'),
    (1, '{"username": "diana", "email": "diana@example.com", "age": 28}'),
    (1, '{"username": "eve", "email": "eve@example.com", "age": 32}'),
    (2, '{"question1": "Great", "question2": "Yes", "rating": 5}'),
    (2, '{"question1": "Good", "question2": "No", "rating": 4}'),
    (2, '{"question1": "Okay", "question2": "Maybe", "rating": 3}'),
    (2, '{"question1": "Poor", "question2": "No", "rating": 2}'),
    (2, '{"question1": "Excellent", "question2": "Yes", "rating": 5}'),
    (3, '{"product": "Widget", "quantity": 10, "price": 100}'),
    (3, '{"product": "Gadget", "quantity": 5, "price": 250}'),
    (3, '{"product": "Gizmo", "quantity": 20, "price": 50}'),
    (3, '{"product": "Thingamajig", "quantity": 15, "price": 75}'),
    (3, '{"product": "Doohickey", "quantity": 8, "price": 150}');


CREATE VIEW user_form_data AS
SELECT 
    fd.id,
    fd.form_id,
    fd.data->>'username' AS username,
    fd.data->>'email' AS email,
    (fd.data->>'age')::INT AS age
FROM form_data fd
JOIN forms f ON fd.form_id = f.id
WHERE f.name = 'user_form';

CREATE VIEW survey_form_data AS
SELECT 
    fd.id,
    fd.form_id,
    fd.data->>'question1' AS question1,
    fd.data->>'question2' AS question2,
    (fd.data->>'rating')::INT AS rating
FROM form_data fd
JOIN forms f ON fd.form_id = f.id
WHERE f.name = 'survey_form';

CREATE VIEW order_form_data AS
SELECT 
    fd.id,
    fd.form_id,
    fd.data->>'product' AS product,
    (fd.data->>'quantity')::INT AS quantity,
    (fd.data->>'price')::INT AS price
FROM form_data fd
JOIN forms f ON fd.form_id = f.id
WHERE f.name = 'order_form';