CREATE TABLE report_objects (
    id serial,
    querystring text,
    sortby text,
    order_by integer,
    include_subagency text,
    query_sql text,
    report_sql text
);

CREATE TABLE activity (
    activityid serial
);

CREATE TABLE qry_column_list (
    col_id serial NOT NULL,
    query integer NOT NULL,
    activity_id integer
);

COPY report_objects (id, querystring, sortby, order_by, include_subagency, query_sql, report_sql) FROM stdin;
1642	\N	\N	\N	\N	\N	\N
\.

COPY activity (activityid) FROM stdin;
16739
\.

COPY qry_column_list (col_id, query, activity_id) FROM stdin;
7298	1642	16739
7299	1642	\N
7300	1642	16739
7301	1642	16739
7302	1642	16739
7303	1642	16739
7304	1642	16739
7305	1642	\N
\.

ALTER TABLE ONLY report_objects
    ADD CONSTRAINT report_objects_pkey PRIMARY KEY (id);

ALTER TABLE ONLY activity
    ADD CONSTRAINT activity_pkey PRIMARY KEY (activityid);

ALTER TABLE ONLY qry_column_list
    ADD CONSTRAINT qry_column_list_pkey PRIMARY KEY (col_id);

ALTER TABLE ONLY qry_column_list
    ADD CONSTRAINT "$2" FOREIGN KEY (activity_id)
    REFERENCES activity(activityid) MATCH FULL
    ON UPDATE CASCADE ON DELETE CASCADE;

CREATE FUNCTION clear_qry_sql() RETURNS "trigger"
    AS '
        begin
              NEW.query_sql := NULL;
              NEW.report_sql := NULL;
           return NEW;
        end;'
    LANGUAGE plpgsql;

CREATE TRIGGER qry_chg_clr_sql
    BEFORE UPDATE ON report_objects
    FOR EACH ROW
    EXECUTE PROCEDURE clear_qry_sql();

CREATE RULE qry_col_del_clr_sql AS
  ON DELETE TO qry_column_list DO
  UPDATE report_objects SET query_sql = NULL::text, report_sql = NULL::text
  WHERE (report_objects.id = old.query);