On Mon, Aug 6, 2018 at 4:36 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
What is the definition for target_date()?
Hi Adrian. Happy to provide this info. Though on a side note, I don't understand why it should matter, if functions are black box optimization fences. But here are the definitions:
CREATE OR REPLACE FUNCTION target_date() RETURNS date AS $$
SELECT target_date FROM target_date_current;
$$ LANGUAGE sql STABLE;The target_date table and views:CREATE TABLE tbl_target_date (
target_date_id SERIAL PRIMARY KEY,
target_date DATE NOT NULL,
effective_at TIMESTAMP NOT NULL DEFAULT current_timestamp,
comment TEXT,
--system fields
added_by INTEGER NOT NULL REFERENCES tbl_staff (staff_id),
added_at TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
changed_by INTEGER NOT NULL REFERENCES tbl_staff (staff_id),
changed_at TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
deleted_at TIMESTAMP(0),
deleted_by INTEGER REFERENCES tbl_staff(staff_id),
deleted_comment TEXT,
sys_log TEXT
);
CREATE VIEW target_date AS SELECT * FROM tbl_target_date WHERE NOT is_deleted;
CREATE VIEW target_date_current AS
SELECT * FROM target_date ORDER BY effective_at DESC LIMIT 1;
CREATE OR REPLACE FUNCTION target_date_no_edit_or_delete() RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP <> 'INSERT')
THEN RAISE EXCEPTION 'Target records cannot be changed or deleted. (Attempted operation: %)',TG_OP;
END IF;
IF (NEW.target_date <> date_trunc('month',NEW.target_date))
THEN RAISE EXCEPTION 'Target date must be the first of a month';
END IF;
IF (NEW.target_date <= target_date())
THEN RAISE EXCEPTION 'Target date can only be moved forward';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER protect_target_date BEFORE INSERT OR UPDATE OR DELETE ON tbl_target_date FOR EACH ROW EXECUTE PROCEDURE target_date_no_edit_or_delete();
CREATE TRIGGER target_date_no_trunacte BEFORE TRUNCATE ON tbl_target_date FOR STATEMENT EXECUTE PROCEDURE target_date_no_edit_or_delete();And the tbl_residence_own, which was referenced in my sample queries:spc=> \d tbl_residence_own
Table "public.tbl_residence_own"
Column | Type | Modifiers
------------------------------+--------------------------------+------------------------------------------------------------------------------
residence_own_id | integer | not null default nextval('tbl_residence_own_residence_own_id_seq'::regclass)
client_id | integer | not null
housing_project_code | character varying(10) | not null
housing_unit_code | character varying(10) | not null
residence_date | date | not null
residence_date_end | date |
unit_rent_manual | numeric(7,2) |
utility_allowance_manual | numeric(7,2) |
is_active_manual | boolean | not null default true
was_received_hap | boolean |
was_received_compliance | boolean |
moved_from_code | character varying(10) |
chronic_homeless_status_code | character varying(10) |
lease_on_file | boolean |
moved_to_code | character varying(10) |
departure_type_code | character varying(10) |
departure_reason_code | character varying(10) |
move_out_was_code | character varying(10) |
returned_homeless | boolean |
was_deposit_returned | boolean |
comment_damage | text |
comment_deposit | text |
comment | text |
old_access_id | character varying |
old_utility_allowance | numeric(9,2) |
added_by | integer | not null
added_at | timestamp(0) without time zone | not null default now()
changed_by | integer | not null
changed_at | timestamp(0) without time zone | not null default now()
is_deleted | boolean | default false
deleted_at | timestamp(0) without time zone |
deleted_by | integer |
deleted_comment | text |
sys_log | text |
tenant_pays_deposit | boolean | not null default false
is_coordinated_entry | boolean |
referral_source | text |
Indexes:
"tbl_residence_own_pkey" PRIMARY KEY, btree (residence_own_id)
"tbl_residence_own_client_id" btree (client_id)
"tbl_residence_own_housing_project_code" btree (housing_project_code)
"tbl_residence_own_housing_unit_code" btree (housing_unit_code)
"tbl_residence_own_is_deleted" btree (is_deleted)
"tbl_residence_own_residence_date" btree (residence_date)
"tbl_residence_own_residence_date_end" btree (residence_date_end)
Check constraints:
"coordinated_entry_or_other" CHECK (xor(is_coordinated_entry, referral_source IS NOT NULL))
"date_sanity" CHECK (residence_date_end IS NULL OR residence_date <= residence_date_end)
Foreign-key constraints:
"tbl_residence_own_added_by_fkey" FOREIGN KEY (added_by) REFERENCES tbl_staff(staff_id)
"tbl_residence_own_changed_by_fkey" FOREIGN KEY (changed_by) REFERENCES tbl_staff(staff_id)
"tbl_residence_own_chronic_homeless_status_code_fkey" FOREIGN KEY (chronic_homeless_status_code) REFERENCES tbl_l_chronic_homeless_status(chronic_homeless_status_code)
"tbl_residence_own_client_id_fkey" FOREIGN KEY (client_id) REFERENCES tbl_client(client_id)
"tbl_residence_own_deleted_by_fkey" FOREIGN KEY (deleted_by) REFERENCES tbl_staff(staff_id)
"tbl_residence_own_departure_reason_code_fkey" FOREIGN KEY (departure_reason_code) REFERENCES tbl_l_departure_reason(departure_reason_code)
"tbl_residence_own_departure_type_code_fkey" FOREIGN KEY (departure_type_code) REFERENCES tbl_l_departure_type(departure_type_code)
"tbl_residence_own_housing_project_code_fkey" FOREIGN KEY (housing_project_code) REFERENCES tbl_l_housing_project(housing_project_code)
"tbl_residence_own_housing_unit_code_fkey" FOREIGN KEY (housing_unit_code) REFERENCES tbl_housing_unit(housing_unit_code)
"tbl_residence_own_move_out_was_code_fkey" FOREIGN KEY (move_out_was_code) REFERENCES tbl_l_exit_status(exit_status_code)
"tbl_residence_own_moved_from_code_fkey" FOREIGN KEY (moved_from_code) REFERENCES tbl_l_facility(facility_code)
"tbl_residence_own_moved_to_code_fkey" FOREIGN KEY (moved_to_code) REFERENCES tbl_l_facility(facility_code)
Triggers:
check_max_occupant AFTER INSERT OR UPDATE ON tbl_residence_own FOR EACH ROW EXECUTE PROCEDURE enforce_max_occupant()
tbl_residence_own_log_chg AFTER INSERT OR DELETE OR UPDATE ON tbl_residence_own FOR EACH ROW EXECUTE PROCEDURE table_log()
tbl_residence_own_no_unit_or_project_change BEFORE UPDATE ON tbl_residence_own FOR EACH ROW EXECUTE PROCEDURE tbl_residence_own_validate_modify()Let me know if I can provide more info. Thanks!
Ken
--
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
