1 DROP SCHEMA action CASCADE;
7 CREATE TABLE action.survey (
9 name TEXT NOT NULL UNIQUE,
10 start_date DATE NOT NULL DEFAULT NOW(),
11 end_date DATE NOT NULL DEFAULT NOW() + '10 years'::INTERVAL,
12 usr_summary BOOL NOT NULL DEFAULT FALSE,
13 opac BOOL NOT NULL DEFAULT FALSE
16 CREATE TABLE action.survey_question (
17 id SERIAL PRIMARY KEY,
18 survey INT NOT NULL REFERENCES action.survey,
19 question TEXT NOT NULL UNIQUE
22 CREATE TABLE action.survey_answer (
23 id SERIAL PRIMARY KEY,
24 question INT NOT NULL REFERENCES action.survey_question,
25 answer TEXT NOT NULL UNIQUE
28 CREATE TABLE action.survey_response (
29 id BIGSERIAL PRIMARY KEY,
30 usr BIGINT NOT NULL, -- REFERENCES actor.usr
31 survey INT NOT NULL REFERENCES action.survey,
32 question INT NOT NULL REFERENCES action.survey_question,
33 answer INT NOT NULL REFERENCES action.survey_answer,
35 effective_date DATE NOT NULL DEFAULT NOW()::DATE
37 CREATE FUNCTION action.survey_response_answer_date_fixup () RETURNS TRIGGER AS $$
39 NEW.anser_date := NOW()::DATE;
42 $$ LANGUAGE 'plpgsql';
43 CREATE TRIGGER action_survey_response_answer_date_fixup_tgr
44 BEFORE INSERT ON action.survey_response
46 EXECUTE PROCEDURE action.survey_response_answer_date_fixup ();