create temporary table pgdump_restore_path(p text);
--
-- NOTE:
--
-- File paths need to be edited. Search for $$PATH$$ and
-- replace it with the path to the directory containing
-- the extracted data files.
--
-- Edit the following to match the path where the
-- tar archive has been extracted.
--
insert into pgdump_restore_path values('/tmp');

--
-- TOC Entry ID 2 (OID 4743732)
--
-- Name: group_seq Type: SEQUENCE Owner: greater
--

CREATE SEQUENCE "group_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 4 (OID 4743734)
--
-- Name: phrase_seq Type: SEQUENCE Owner: greater
--

CREATE SEQUENCE "phrase_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 6 (OID 4743736)
--
-- Name: bin_obj_seq Type: SEQUENCE Owner: greater
--

CREATE SEQUENCE "bin_obj_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 8 (OID 4743738)
--
-- Name: subst_seq Type: SEQUENCE Owner: greater
--

CREATE SEQUENCE "subst_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 10 (OID 4743740)
--
-- Name: subst_det_seq Type: SEQUENCE Owner: greater
--

CREATE SEQUENCE "subst_det_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 12 (OID 4743742)
--
-- Name: env_seq Type: SEQUENCE Owner: greater
--

CREATE SEQUENCE "env_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 14 (OID 4743744)
--
-- Name: env_det_seq Type: SEQUENCE Owner: greater
--

CREATE SEQUENCE "env_det_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 16 (OID 4743746)
--
-- Name: market_seq Type: SEQUENCE Owner: greater
--

CREATE SEQUENCE "market_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 18 (OID 4743748)
--
-- Name: mod_seq Type: SEQUENCE Owner: greater
--

CREATE SEQUENCE "mod_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 20 (OID 4743750)
--
-- Name: mod_det_seq Type: SEQUENCE Owner: greater
--

CREATE SEQUENCE "mod_det_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 22 (OID 4743752)
--
-- Name: res_seq Type: SEQUENCE Owner: greater
--

CREATE SEQUENCE "res_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 24 (OID 4743754)
--
-- Name: res_stretch_seq Type: SEQUENCE Owner: greater
--

CREATE SEQUENCE "res_stretch_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 26 (OID 4743756)
--
-- Name: res_disch_seq Type: SEQUENCE Owner: greater
--

CREATE SEQUENCE "res_disch_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 28 (OID 4743758)
--
-- Name: sess_seq Type: SEQUENCE Owner: greater
--

CREATE SEQUENCE "sess_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 30 (OID 4743760)
--
-- Name: sess_disch_seq Type: SEQUENCE Owner: greater
--

CREATE SEQUENCE "sess_disch_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 32 (OID 4743762)
--
-- Name: catch_seq Type: SEQUENCE Owner: greater
--

CREATE SEQUENCE "catch_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 35 (OID 4743764)
--
-- Name: bin_obj_search_tab Type: TABLE Owner: greater
--

CREATE TABLE "bin_obj_search_tab" (
	"bin_obj_id" numeric(26,0) NOT NULL,
	"key_word" character varying(255) NOT NULL
);

--
-- TOC Entry ID 36 (OID 4743768)
--
-- Name: bin_obj_tab Type: TABLE Owner: greater
--

CREATE TABLE "bin_obj_tab" (
	"bin_obj_id" numeric(26,0) DEFAULT nextval('BIN_OBJ_SEQ'::text) NOT NULL,
	"object_id" numeric(26,0) NOT NULL,
	"name" character varying(255) NOT NULL,
	"user_id" character varying(30) NOT NULL,
	"privs" numeric(1,0) NOT NULL,
	"obj_type" character varying(30) NOT NULL,
	"obj_sub_type" character varying(30) NOT NULL,
	"file_type" character varying(3) NOT NULL,
	"file_obj" oid NOT NULL,
	"file_size" numeric(26,0) NOT NULL,
	"remark" character varying(2000),
	"cre_date" date NOT NULL,
	"mod_date" date NOT NULL,
	Constraint "bin_obj_u1_idx" Primary Key ("bin_obj_id")
);

--
-- TOC Entry ID 88 (OID 4743776)
--
-- Name: "replace" (character varying,character varying,character varying) Type: FUNCTION Owner: greater
--

CREATE FUNCTION "replace" (character varying,character varying,character varying) RETURNS character varying AS '
declare
    string alias for $1;
    sub alias for $2;
    replacement alias for $3;
    match integer;
    end_before integer;
    start_after integer;
    string_replaced varchar;
    string_remainder varchar;
begin
    string_remainder := string;
    string_replaced := '''';
    match := position(sub in string_remainder);

    while match > 0 loop
        end_before := match - 1;
        start_after := match + length(sub);
        string_replaced := string_replaced || substr(string_remainder, 1, end_before) || replacement;
        string_remainder := substr(string_remainder, start_after);
        match := position(sub in string_remainder);
    end loop;
    string_replaced := string_replaced || string_remainder;

    return string_replaced;
end;
' LANGUAGE 'plpgsql';

--
-- TOC Entry ID 89 (OID 4743777)
--
-- Name: "bin_obj_procf" () Type: FUNCTION Owner: greater
--

CREATE FUNCTION "bin_obj_procf" () RETURNS TRIGGER AS '
DECLARE
  updateAction VARCHAR (10);
  remarkString VARCHAR (2001);
  separatorPos INT;
BEGIN
  updateAction := ''nothing'';

  IF TG_OP=''UPDATE'' THEN
    IF (OLD.REMARK IS NOT NULL OR NEW.REMARK IS NOT NULL) THEN
      IF NEW.REMARK IS NULL OR LENGTH(NEW.REMARK) = 0 THEN
        updateAction := ''delete'';
      ELSIF OLD.REMARK IS NULL OR LENGTH(OLD.REMARK) = 0 THEN
        updateAction := ''insert'';
      ELSIF OLD.REMARK != NEW.REMARK THEN
        updateAction := ''rebuild'';
      END IF;
    END IF;
  END IF;
  IF (TG_OP=''DELETE'' OR updateAction = ''delete'' OR updateAction = ''rebuild'') THEN
    DELETE FROM BIN_OBJ_SEARCH_TAB WHERE BIN_OBJ_ID = OLD.BIN_OBJ_ID;
    RETURN OLD;
  END IF;
  IF ((TG_OP=''INSERT'' AND NEW.REMARK IS NOT NULL) OR updateAction = ''insert'' OR updateAction = ''rebuild'') THEN
    remarkString := NEW.REMARK;
    remarkString := UPPER(remarkString);
    remarkString := REPLACE(remarkString, chr(39), '''');
    remarkString := REPLACE(remarkString, chr(96), '''');
    remarkString := REPLACE(remarkString, chr(180), '''');
    remarkString := REPLACE(remarkString, '' '', '';'');
    remarkString := REPLACE(remarkString, '','', '';'');
    remarkString := REPLACE(remarkString, ''.'', '';'');
    remarkString := REPLACE(remarkString, '':'', '';'');
    remarkString := REPLACE(remarkString, ''('', '';'');
    remarkString := REPLACE(remarkString, '')'', '';'');
    remarkString := LTRIM(remarkString, '';'');
    remarkString := RTRIM(remarkString, '';'');
    remarkString := remarkString || '';'';
    WHILE (LENGTH(remarkString) > 0) LOOP
      separatorPos := strpos(remarkString, '';'');
      INSERT INTO BIN_OBJ_SEARCH_TAB
             (BIN_OBJ_ID, KEY_WORD)
      VALUES (NEW.BIN_OBJ_ID, substr(remarkString, 1, separatorPos - 1));
      remarkString := substr(remarkString, separatorPos,
                              LENGTH (remarkString) - separatorPos + 1);
      remarkString := LTRIM (remarkString, '';'');
    END LOOP;
  END IF;
  RETURN NEW;
END;
' LANGUAGE 'plpgsql';

--
-- TOC Entry ID 37 (OID 4743779)
--
-- Name: group_tab Type: TABLE Owner: greater
--

CREATE TABLE "group_tab" (
	"group_id" numeric(26,0) DEFAULT nextval('GROUP_SEQ'::text) NOT NULL,
	"name" character varying(255) NOT NULL,
	"admin" character varying(1) NOT NULL,
	"cre_date" date NOT NULL,
	"mod_date" date NOT NULL,
	"status" character varying(30),
	"remark" character varying(2000),
	Constraint "group_u1_idx" Primary Key ("group_id")
);

--
-- TOC Entry ID 38 (OID 4743786)
--
-- Name: message_tab Type: TABLE Owner: greater
--

CREATE TABLE "message_tab" (
	"message_id" numeric(26,0) NOT NULL,
	"language" character varying(1) NOT NULL,
	"type" character varying(3) NOT NULL,
	"message" character varying(255),
	Constraint "message_u1_idx" Primary Key ("message_id")
);

--
-- TOC Entry ID 39 (OID 4743790)
--
-- Name: phrase_tab Type: TABLE Owner: greater
--

CREATE TABLE "phrase_tab" (
	"phrase_id" numeric(26,0) DEFAULT nextval('PHRASE_SEQ'::text) NOT NULL,
	"phrase_grp_id" numeric(26,0) NOT NULL,
	"language" character varying(1) NOT NULL,
	"sort" numeric(3,0) NOT NULL,
	"value_pt" character varying(255),
	"remark" character varying(2000),
	Constraint "phrase_u1_idx" Primary Key ("phrase_id")
);

--
-- TOC Entry ID 40 (OID 4743797)
--
-- Name: para_tree_def_tab Type: TABLE Owner: greater
--

CREATE TABLE "para_tree_def_tab" (
	"field_id" character varying(30) NOT NULL,
	"block_id" character varying(30) NOT NULL,
	"group_id" character varying(30) NOT NULL,
	"obj_type" character varying(30) NOT NULL,
	"var_type" character varying(30) NOT NULL,
	"tree_level" numeric(1,0) NOT NULL,
	"field_sort" numeric(3,0) NOT NULL,
	"block_sort" numeric(3,0) NOT NULL,
	"group_sort" numeric(3,0) NOT NULL,
	"label" character varying(255),
	"default_val" character varying(255),
	"form_mask" character varying(255),
	"unit" character varying(30),
	"w_range" character varying(255),
	"e_range" character varying(255),
	"remark" character varying(2000),
	Constraint "para_tree_u1_idx" Primary Key ("group_id", "block_id", "field_id")
);

--
-- TOC Entry ID 41 (OID 4743804)
--
-- Name: user_tab Type: TABLE Owner: greater
--

CREATE TABLE "user_tab" (
	"user_id" character varying(30) NOT NULL,
	"group_id" numeric(26,0) NOT NULL,
	"cre_date" date NOT NULL,
	"mod_date" date NOT NULL,
	"status" character varying(30) NOT NULL,
	"remark" character varying(2000),
	Constraint "user_u1_idx" Primary Key ("user_id")
);

--
-- TOC Entry ID 42 (OID 4743810)
--
-- Name: catch_tab Type: TABLE Owner: greater
--

CREATE TABLE "catch_tab" (
	"catch_id" numeric(26,0) DEFAULT nextval('CATCH_SEQ'::text) NOT NULL,
	"user_id" character varying(30) NOT NULL,
	"cre_date" date NOT NULL,
	"mod_date" date NOT NULL,
	"status" character varying(30),
	"privs" numeric(1,0) NOT NULL,
	"name" character varying(255),
	"remark" character varying(2000),
	Constraint "catch_u1_idx" Primary Key ("catch_id")
);

--
-- TOC Entry ID 90 (OID 4743817)
--
-- Name: "catch_procf" () Type: FUNCTION Owner: greater
--

CREATE FUNCTION "catch_procf" () RETURNS TRIGGER AS '
    BEGIN
 	IF TG_OP=''UPDATE'' THEN
	  IF OLD.CATCH_ID  != NEW.CATCH_ID THEN
	      UPDATE  BIN_OBJ_TAB
	      SET     OBJECT_ID    = NEW.CATCH_ID
	      WHERE   OBJECT_ID    = OLD.CATCH_ID
	      AND     OBJ_TYPE     = ''CATCHMENT'';
	  END IF;
	END IF;
	IF TG_OP=''DELETE'' THEN
	    DELETE FROM BIN_OBJ_TAB
	    WHERE   OBJECT_ID    = OLD.CATCH_ID
	    AND     OBJ_TYPE     = ''CATCHMENT'';
	END IF;
        RETURN NEW;
    END;
' LANGUAGE 'plpgsql';

--
-- TOC Entry ID 43 (OID 4743819)
--
-- Name: backgrd_join_tab Type: TABLE Owner: greater
--

CREATE TABLE "backgrd_join_tab" (
	"catch_id" numeric(26,0) NOT NULL,
	"bin_obj_id" numeric(26,0) NOT NULL,
	Constraint "backgrd_u1_idx" Primary Key ("catch_id", "bin_obj_id")
);

--
-- TOC Entry ID 44 (OID 4743822)
--
-- Name: disch_class_tab Type: TABLE Owner: greater
--

CREATE TABLE "disch_class_tab" (
	"disch_class_id" numeric(26,0) NOT NULL,
	"name" character varying(255),
	"wwtp_type" character varying(255),
	"capacity" character varying(40),
	"aer_type" character varying(40),
	"h_prim" character varying(40),
	"v_overflow_prim" character varying(40),
	"r_ss_prim_max" character varying(40),
	"h_sec" character varying(40),
	"v_overflow_sec" character varying(40),
	"r_ss_sec_max" character varying(40),
	"ss_ml" character varying(40),
	"recycle" character varying(40),
	"a_biomass" character varying(40),
	"h_act_sludge" character varying(40),
	"b_x" character varying(40),
	"y" character varying(40),
	"do_" character varying(40),
	"f_aerobic" character varying(40),
	"f_anoxic" character varying(40),
	"f_anaerobic" character varying(40),
	"eps_n" character varying(40),
	"eps_dn" character varying(40),
	"remark" character varying(2000),
	Constraint "disch_class_u1_idx" Primary Key ("disch_class_id")
);

--
-- TOC Entry ID 45 (OID 4743829)
--
-- Name: stretch_class_tab Type: TABLE Owner: greater
--

CREATE TABLE "stretch_class_tab" (
	"stretch_class_id" numeric(26,0) NOT NULL,
	"name" character varying(255),
	"ss_val" character varying(255),
	"ss_dist" character varying(11),
	"f_oc" character varying(40),
	"ph_val" character varying(255),
	"ph_dist" character varying(11),
	"k_z" character varying(40),
	"x" character varying(40),
	"do_val" character varying(255),
	"do_dist" character varying(11),
	"temp_water_val" character varying(255),
	"temp_water_dist" character varying(11),
	"temp_air_val" character varying(255),
	"temp_air_dist" character varying(11),
	"v_sed" character varying(40),
	"eps_sed" character varying(40),
	"rho_sed" character varying(40),
	"v_wind10_val" character varying(255),
	"v_wind10_dist" character varying(11),
	"remark" character varying(2000),
	Constraint "stretch_class_u1_idx" Primary Key ("stretch_class_id")
);

--
-- TOC Entry ID 46 (OID 4743836)
--
-- Name: subst_tab Type: TABLE Owner: greater
--

CREATE TABLE "subst_tab" (
	"subst_id" numeric(26,0) DEFAULT nextval('SUBST_SEQ'::text) NOT NULL,
	"name" character varying(255) NOT NULL,
	"user_id" character varying(30) NOT NULL,
	"cre_date" date NOT NULL,
	"mod_date" date NOT NULL,
	"status" character varying(30) NOT NULL,
	"privs" numeric(1,0) NOT NULL,
	"is_templ" character varying(1) NOT NULL,
	"remark" character varying(2000),
	Constraint "subst_u1_idx" Primary Key ("subst_id")
);

--
-- TOC Entry ID 47 (OID 4743843)
--
-- Name: subst_det_tab Type: TABLE Owner: greater
--

CREATE TABLE "subst_det_tab" (
	"subst_det_id" numeric(26,0) DEFAULT nextval('SUBST_DET_SEQ'::text) NOT NULL,
	"subst_id" numeric(26,0) NOT NULL,
	"field_id" character varying(30) NOT NULL,
	"block_id" character varying(30) NOT NULL,
	"block_recno" numeric(3,0) NOT NULL,
	"value_sdt" character varying(255),
	"dist_type" character varying(11),
	"remark" character varying(2000),
	Constraint "subst_det_u1_idx" Primary Key ("subst_det_id")
);

--
-- TOC Entry ID 48 (OID 4743850)
--
-- Name: env_tab Type: TABLE Owner: greater
--

CREATE TABLE "env_tab" (
	"env_id" numeric(26,0) DEFAULT nextval('ENV_SEQ'::text) NOT NULL,
	"name" character varying(255) NOT NULL,
	"user_id" character varying(30) NOT NULL,
	"cre_date" date NOT NULL,
	"mod_date" date NOT NULL,
	"status" character varying(30) NOT NULL,
	"privs" numeric(1,0) NOT NULL,
	"is_templ" character varying(1) NOT NULL,
	"remark" character varying(2000),
	Constraint "env_u1_idx" Primary Key ("env_id")
);

--
-- TOC Entry ID 49 (OID 4743857)
--
-- Name: env_det_tab Type: TABLE Owner: greater
--

CREATE TABLE "env_det_tab" (
	"env_det_id" numeric(26,0) DEFAULT nextval('ENV_DET_SEQ'::text) NOT NULL,
	"env_id" numeric(26,0) NOT NULL,
	"field_id" character varying(30) NOT NULL,
	"block_id" character varying(30) NOT NULL,
	"block_recno" numeric(3,0) NOT NULL,
	"value_edt" character varying(255),
	"dist_type" character varying(11),
	"remark" character varying(2000),
	Constraint "env_det_u1_idx" Primary Key ("env_det_id")
);

--
-- TOC Entry ID 50 (OID 4743864)
--
-- Name: stretch_tab Type: TABLE Owner: greater
--

CREATE TABLE "stretch_tab" (
	"stretch_id" numeric(26,0) NOT NULL,
	"catch_id" numeric(26,0) NOT NULL,
	"stretch_class_id" numeric(26,0) NOT NULL,
	"up1_id" numeric(26,0),
	"up2_id" numeric(26,0),
	"down1_id" numeric(26,0),
	"down2_id" numeric(26,0),
	"name" character varying(255),
	"type" character varying(11) NOT NULL,
	"q_val" character varying(255) NOT NULL,
	"q_dist" character varying(11),
	"v_val" character varying(255) NOT NULL,
	"v_dist" character varying(11),
	"l" character varying(40) NOT NULL,
	"d_val" character varying(255) NOT NULL,
	"d_dist" character varying(11),
	Constraint "stretch_u1_idx" Primary Key ("catch_id", "stretch_id")
);

--
-- TOC Entry ID 51 (OID 4743871)
--
-- Name: disch_tab Type: TABLE Owner: greater
--

CREATE TABLE "disch_tab" (
	"disch_id" numeric(26,0) NOT NULL,
	"stretch_id" numeric(26,0) NOT NULL,
	"catch_id" numeric(26,0) NOT NULL,
	"disch_class_id" numeric(26,0) NOT NULL,
	"name" character varying(255),
	"pop" character varying(40),
	"dwf" character varying(40),
	"flow_val" character varying(255),
	"flow_dist" character varying(11),
	"flow_dom" character varying(40),
	"flow_nondom" character varying(40),
	"flow_runoff" character varying(40),
	"treated" character varying(40),
	"r_wwtp" character varying(40),
	"remark" character varying(2000),
	Constraint "disch_u1_idx" Primary Key ("catch_id", "disch_id")
);

--
-- TOC Entry ID 52 (OID 4743878)
--
-- Name: market_tab Type: TABLE Owner: greater
--

CREATE TABLE "market_tab" (
	"market_id" numeric(26,0) DEFAULT nextval('MARKET_SEQ'::text) NOT NULL,
	"disch_id" numeric(26,0) NOT NULL,
	"catch_id" numeric(26,0) NOT NULL,
	"name" character varying(255) NOT NULL,
	"user_id" character varying(30) NOT NULL,
	"cre_date" date NOT NULL,
	"mod_date" date NOT NULL,
	"status" character varying(30) NOT NULL,
	"privs" numeric(1,0) NOT NULL,
	"consumption" character varying(40),
	"flux_nondom" character varying(40),
	"flux_runoff" character varying(40),
	"remark" character varying(2000),
	Constraint "market_u1_idx" Primary Key ("catch_id", "disch_id", "market_id")
);

--
-- TOC Entry ID 53 (OID 4743884)
--
-- Name: mod_tab Type: TABLE Owner: greater
--

CREATE TABLE "mod_tab" (
	"mod_id" numeric(26,0) DEFAULT nextval('MOD_SEQ'::text) NOT NULL,
	"name" character varying(255) NOT NULL,
	"user_id" character varying(30) NOT NULL,
	"cre_date" date NOT NULL,
	"mod_date" date NOT NULL,
	"status" character varying(30) NOT NULL,
	"privs" numeric(1,0) NOT NULL,
	"remark" character varying(2000),
	Constraint "mod_u1_idx" Primary Key ("mod_id")
);

--
-- TOC Entry ID 54 (OID 4743891)
--
-- Name: mod_det_tab Type: TABLE Owner: greater
--

CREATE TABLE "mod_det_tab" (
	"mod_det_id" numeric(26,0) DEFAULT nextval('MOD_DET_SEQ'::text) NOT NULL,
	"mod_id" numeric(26,0) NOT NULL,
	"field_id" character varying(30) NOT NULL,
	"block_id" character varying(30) NOT NULL,
	"block_recno" numeric(3,0) NOT NULL,
	"value_mdt" character varying(255),
	"dist_type" character varying(11),
	"remark" character varying(2000),
	Constraint "mod_det_u1_idx" Primary Key ("mod_det_id")
);

--
-- TOC Entry ID 55 (OID 4743898)
--
-- Name: res_tab Type: TABLE Owner: greater
--

CREATE TABLE "res_tab" (
	"res_id" numeric(26,0) DEFAULT nextval('RES_SEQ'::text) NOT NULL,
	"river_factor" character varying(40) NOT NULL,
	"river_unit" character varying(255) NOT NULL,
	"stp_factor" character varying(40) NOT NULL,
	"stp_unit" character varying(255) NOT NULL,
	"pec_initial" character varying(2000) NOT NULL,
	"pec_catch" character varying(2000) NOT NULL,
	"remark" character varying(2000),
	Constraint "res_u1_idx" Primary Key ("res_id")
);

--
-- TOC Entry ID 91 (OID 4743904)
--
-- Name: "res_procf" () Type: FUNCTION Owner: greater
--

CREATE FUNCTION "res_procf" () RETURNS TRIGGER AS '
BEGIN
  IF TG_OP=''UPDATE'' THEN
    IF OLD.RES_ID  != NEW.RES_ID THEN
      UPDATE  BIN_OBJ_TAB
      SET     OBJECT_ID    = NEW.RES_ID
      WHERE   OBJECT_ID    = OLD.RES_ID
      AND     OBJ_TYPE     = ''RESULT''
      AND (   OBJ_SUB_TYPE = ''DISCH''
           OR OBJ_SUB_TYPE = ''STRETCH'');
    END IF;
  END IF;
  IF TG_OP=''DELETE'' THEN
    DELETE FROM BIN_OBJ_TAB
    WHERE   OBJECT_ID    = OLD.RES_ID
    AND     OBJ_TYPE     = ''RESULT''
    AND (   OBJ_SUB_TYPE = ''DISCH''
         OR OBJ_SUB_TYPE = ''STRETCH'');
  END IF;
  RETURN NEW;
END;
' LANGUAGE 'plpgsql';

--
-- TOC Entry ID 56 (OID 4743906)
--
-- Name: res_disch_tab Type: TABLE Owner: greater
--

CREATE TABLE "res_disch_tab" (
	"res_disch_id" numeric(26,0) DEFAULT nextval('RES_DISCH_SEQ'::text) NOT NULL,
	"res_id" numeric(26,0) NOT NULL,
	"disch_id" numeric(26,0) NOT NULL,
	"field_id" character varying(30) NOT NULL,
	"block_id" character varying(30) NOT NULL,
	"block_recno" numeric(3,0) NOT NULL,
	"value_rdt" character varying(255),
	"dist_type" character varying(11),
	"remark" character varying(2000),
	Constraint "res_disch_u1_idx" Primary Key ("res_disch_id")
);

--
-- TOC Entry ID 57 (OID 4743913)
--
-- Name: res_stretch_tab Type: TABLE Owner: greater
--

CREATE TABLE "res_stretch_tab" (
	"res_stretch_id" numeric(26,0) DEFAULT nextval('RES_STRETCH_SEQ'::text) NOT NULL,
	"res_id" numeric(26,0) NOT NULL,
	"stretch_id" numeric(26,0) NOT NULL,
	"field_id" character varying(30) NOT NULL,
	"block_id" character varying(30) NOT NULL,
	"block_recno" numeric(3,0) NOT NULL,
	"value_rst" character varying(255),
	"dist_type" character varying(11),
	"remark" character varying(2000),
	Constraint "res_stretch_u1_idx" Primary Key ("res_stretch_id")
);

--
-- TOC Entry ID 58 (OID 4743920)
--
-- Name: sess_tab Type: TABLE Owner: greater
--

CREATE TABLE "sess_tab" (
	"sess_id" numeric(26,0) DEFAULT nextval('SESS_SEQ'::text) NOT NULL,
	"catch_id" numeric(26,0),
	"subst_id" numeric(26,0),
	"env_id" numeric(26,0),
	"market_id" numeric(26,0),
	"mod_id" numeric(26,0),
	"res_id" numeric(26,0),
	"name" character varying(255) NOT NULL,
	"user_id" character varying(30) NOT NULL,
	"cre_date" date NOT NULL,
	"mod_date" date NOT NULL,
	"status" character varying(30) NOT NULL,
	"privs" numeric(1,0) NOT NULL,
	"is_temp_sess" character varying(1) NOT NULL,
	"temp_sess_src_id" numeric(26,0),
	"temp_sess_db_sid" oid,
	"remark" character varying(2000),
	"sess_disch_id" numeric(26,0),
	Constraint "sess_u1_idx" Primary Key ("sess_id")
);

--
-- TOC Entry ID 59 (OID 4743930)
--
-- Name: sess_disch_tab Type: TABLE Owner: greater
--

CREATE TABLE "sess_disch_tab" (
	"sess_disch_id" numeric(26,0) DEFAULT nextval('SESS_DISCH_SEQ'::text) NOT NULL,
	"disch_id" numeric(26,0) NOT NULL,
	"stretch_id" numeric(26,0) NOT NULL,
	"catch_id" numeric(26,0) NOT NULL,
	"disch_class_id" numeric(26,0) NOT NULL,
	"name" character varying(255) NOT NULL,
	"user_id" character varying(30) NOT NULL,
	"cre_date" date NOT NULL,
	"mod_date" date NOT NULL,
	"status" character varying(30) NOT NULL,
	"privs" numeric(1,0) NOT NULL,
	"pop" character varying(40),
	"dwf" character varying(40),
	"flow_val" character varying(255),
	"flow_dist" character varying(11),
	"flow_dom" character varying(40),
	"flow_nondom" character varying(40),
	"flow_runoff" character varying(40),
	"treated" character varying(40),
	"r_wwtp" character varying(40),
	"remark" character varying(2000),
	Constraint "sess_disch_u1_idx" Primary Key ("catch_id", "disch_id", "sess_disch_id")
);

--
-- TOC Entry ID 92 (OID 4743937)
--
-- Name: "instr" (character varying,character varying) Type: FUNCTION Owner: greater
--

CREATE FUNCTION "instr" (character varying,character varying) RETURNS integer AS '
 DECLARE
     pos integer;
 BEGIN
     pos:= instr($1,$2,1);
     RETURN pos;
 END;
 ' LANGUAGE 'plpgsql';

--
-- TOC Entry ID 93 (OID 4743938)
--
-- Name: "instr" (character varying,character varying,integer) Type: FUNCTION Owner: greater
--

CREATE FUNCTION "instr" (character varying,character varying,integer) RETURNS integer AS '
 DECLARE
     string ALIAS FOR $1;
     string_to_search ALIAS FOR $2;
     beg_index ALIAS FOR $3;
     pos integer NOT NULL DEFAULT 0;
     temp_str VARCHAR;
     beg INTEGER;
     length INTEGER;
     ss_length INTEGER;
 BEGIN
     IF beg_index > 0 THEN
 
        temp_str := substring(string FROM beg_index);
        pos := position(string_to_search IN temp_str);
 
        IF pos = 0 THEN
          RETURN 0;
      ELSE
          RETURN pos + beg_index - 1;
      END IF;
     ELSE
        ss_length := char_length(string_to_search);
        length := char_length(string);
        beg := length + beg_index - ss_length + 2;
 
        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
          pos := position(string_to_search IN temp_str);
 
          IF pos > 0 THEN
            RETURN beg;
          END IF;
 
          beg := beg - 1;
        END LOOP;
        RETURN 0;
     END IF;
 END;
 ' LANGUAGE 'plpgsql';

--
-- TOC Entry ID 94 (OID 4743939)
--
-- Name: "instr" (character varying,character varying,integer,integer) Type: FUNCTION Owner: greater
--

CREATE FUNCTION "instr" (character varying,character varying,integer,integer) RETURNS integer AS '
 DECLARE
     string ALIAS FOR $1;
     string_to_search ALIAS FOR $2;
     beg_index ALIAS FOR $3;
     occur_index ALIAS FOR $4;
     pos integer NOT NULL DEFAULT 0;
     occur_number INTEGER NOT NULL DEFAULT 0;
     temp_str VARCHAR;
     beg INTEGER;
     i INTEGER;
     length INTEGER;
     ss_length INTEGER;
 BEGIN
     IF beg_index > 0 THEN
         beg := beg_index;
         temp_str := substring(string FROM beg_index);
 
         FOR i IN 1..occur_index LOOP
             pos := position(string_to_search IN temp_str);
 
             IF i = 1 THEN
                 beg := beg + pos - 1;
             ELSE
                 beg := beg + pos;
             END IF;
 
             temp_str := substring(string FROM beg + 1);
         END LOOP;
 
         IF pos = 0 THEN
             RETURN 0;
         ELSE
             RETURN beg;
         END IF;
     ELSE
         ss_length := char_length(string_to_search);
         length := char_length(string);
         beg := length + beg_index - ss_length + 2;
 
         WHILE beg > 0 LOOP
             temp_str := substring(string FROM beg FOR ss_length);
             pos := position(string_to_search IN temp_str);
 
             IF pos > 0 THEN
                 occur_number := occur_number + 1;
 
                 IF occur_number = occur_index THEN
                     RETURN beg;
                 END IF;
             END IF;
 
             beg := beg - 1;
         END LOOP;
 
         RETURN 0;
     END IF;
 END;
 ' LANGUAGE 'plpgsql';

--
-- TOC Entry ID 95 (OID 4743940)
--
-- Name: "dbms_init_seq" () Type: FUNCTION Owner: greater
--

CREATE FUNCTION "dbms_init_seq" () RETURNS integer AS '
	DECLARE
	  v_diff   NUMERIC(26);			-- tmp Variables 
	  v_curr   NUMERIC(26);
	  v_max    NUMERIC(26);
	BEGIN					-- get max id of table 
						-- and current seq value (csv)
	  SELECT coalesce(MAX(ENV_DET_ID),1)      	INTO v_max   FROM ENV_DET_TAB;
	  SELECT nextval(''ENV_DET_SEQ'')		INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''ENV_DET_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(BIN_OBJ_ID),1)      	INTO v_max   FROM BIN_OBJ_TAB;
	  SELECT nextval(''BIN_OBJ_SEQ'')		INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''BIN_OBJ_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(ENV_ID),1)      		INTO v_max   FROM ENV_TAB;
	  SELECT nextval(''ENV_SEQ'')			INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''ENV_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(GROUP_ID),1)      	INTO v_max   FROM GROUP_TAB;
	  SELECT nextval(''GROUP_SEQ'')			INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''GROUP_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(MARKET_ID),1)      	INTO v_max   FROM MARKET_TAB;
	  SELECT nextval(''MARKET_SEQ'')		INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''MARKET_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(MOD_DET_ID),1)      	INTO v_max   FROM MOD_DET_TAB;
	  SELECT nextval(''MOD_DET_SEQ'')		INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''MOD_DET_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(MOD_ID),1)	      	INTO v_max   FROM MOD_TAB;
	  SELECT nextval(''MOD_SEQ'')			INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''MOD_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(PHRASE_ID),1)      	INTO v_max   FROM PHRASE_TAB;
	  SELECT nextval(''PHRASE_SEQ'')		INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''PHRASE_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(CATCH_ID),1)      	INTO v_max   FROM CATCH_TAB;
	  SELECT nextval(''CATCH_SEQ'')			INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''CATCH_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(RES_DISCH_ID),1)      	INTO v_max   FROM RES_DISCH_TAB;
	  SELECT nextval(''RES_DISCH_SEQ'')		INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''RES_DISCH_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(RES_ID),1)      		INTO v_max   FROM RES_TAB;
	  SELECT nextval(''RES_SEQ'')			INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''RES_SEQ'',v_diff + 1);
	  END IF;

  	  SELECT coalesce(MAX(SUBST_DET_ID),1)      	INTO v_max   FROM SUBST_DET_TAB;
	  SELECT nextval(''SUBST_DET_SEQ'')		INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''SUBST_DET_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(SUBST_ID),1)      	INTO v_max   FROM SUBST_TAB;
	  SELECT nextval(''SUBST_SEQ'')			INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''SUBST_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(SESS_DISCH_ID),1)      	INTO v_max   FROM SESS_DISCH_TAB;
	  SELECT nextval(''SESS_DISCH_SEQ'')		INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''SESS_DISCH_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(SESS_ID),1)      		INTO v_max   FROM SESS_TAB;
	  SELECT nextval(''SESS_SEQ'')			INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''SESS_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(RES_STRETCH_ID),1)      	INTO v_max   FROM RES_STRETCH_TAB;
	  SELECT nextval(''RES_STRETCH_SEQ'')		INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''RES_STRETCH_SEQ'',v_diff + 1);
	  END IF;	 
	  RETURN 1;
	END;
' LANGUAGE 'plpgsql';

--
-- TOC Entry ID 96 (OID 4743941)
--
-- Name: "pg_decode" (numeric,numeric,numeric,numeric) Type: FUNCTION Owner: greater
--

CREATE FUNCTION "pg_decode" (numeric,numeric,numeric,numeric) RETURNS numeric AS '
DECLARE 
	output_value numeric;
BEGIN
	SELECT CASE WHEN $1=$2 THEN $3
                        ELSE $4
	       END
		   INTO output_value;
	return output_value; 	   
END;
' LANGUAGE 'plpgsql';


