CREATE TABLESPACE ext LOCATION '/data/index/ext';

CREATE ROLE root;

CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL';

CREATE TABLE t_a (
    int1 INTEGER NOT NULL PRIMARY KEY,
    str1 CHARACTER(20),
    bool1 boolean
);

CREATE VIEW a_v1 AS
SELECT DISTINCT(str1),bool1 FROM t_a WHERE bool1 IS TRUE ORDER BY str1;

CREATE VIEW a_v2 AS SELECT distinct(str1),count(*) FROM t_a GROUP BY t_a.str1;

CREATE TABLE t_b (
    int1 INTEGER NOT NULL,
    str2 CHARACTER VARYING(20) NOT NULL,
    bool1 BOOLEAN
);

CREATE TABLE t_c (
	str1 CHAR(20) NOT NULL,
	str2 VARCHAR(20) NOT NULL PRIMARY KEY
);

CREATE TABLE ifl (
	recno SERIAL PRIMARY KEY,
	int1 INTEGER NOT NULL
);

DROP FUNCTION add_str1tot_a();
CREATE OR REPLACE FUNCTION add_str1tot_a()
RETURNS TRIGGER AS $ptot_a$
DECLARE
	temp VARCHAR(20);
BEGIN
	SELECT p.str1 INTO temp FROM t_c p WHERE p.str2=NEW.str2;
	UPDATE t_a SET str1=temp WHERE str1 IS NULL AND int1=NEW.int1;
	RETURN NULL;
END;
$ptot_a$ LANGUAGE plpgsql;

DROP FUNCTION sett_astr1bool1();
CREATE OR REPLACE FUNCTION sett_astr1bool1()
RETURNS TRIGGER as $sepi$
DECLARE
	ig BOOLEAN;
BEGIN
	IF NEW.str1 IS NOT NULL AND NEW.bool1 IS NULL THEN
		SELECT b.bool1 INTO ig FROM a_v1 b WHERE b.str1=NEW.str1;
		IF ig IS NOT NULL THEN
			UPDATE t_a SET bool1=ig WHERE int1=NEW.int1;
		END IF;
	END IF;
	RETURN NULL;
END;
$sepi$ LANGUAGE plpgsql;

DROP FUNCTION sett_abool1();
CREATE OR REPLACE FUNCTION sett_abool1()
RETURNS TRIGGER as $sei$
DECLARE
	temp BOOLEAN;
	temp2 CHAR(20);
BEGIN
	SELECT b.bool1 INTO temp FROM badt_b b WHERE (b.str2=NEW.str2) AND
	    (bool1 IS NOT NULL);
	IF temp IS NOT NULL THEN
		UPDATE t_b SET bool1=temp WHERE str2=NEW.str2;
	END IF;
	SELECT t.str1 INTO temp2 FROM t_a t WHERE t.int1=NEW.int1;
	IF temp2 IS NULL THEN
		SELECT u.str1 INTO temp2 FROM t_c u WHERE u.str2=NEW.str2;
		IF temp2 IS NOT NULL THEN
			IF temp IS NOT NULL THEN
				UPDATE t_a SET str1=temp2,bool1=temp
				    WHERE int1=NEW.int1;
			ELSE
				UPDATE t_a SET str1=temp2 WHERE int1=NEW.int1;
			END IF;
		ELSE
			IF temp IS NOT NULL THEN
				UPDATE t_a SET bool1=temp WHERE int1=NEW.int1;
			END IF;
		END IF;
	ELSE
		IF temp IS NOT NULL THEN
			UPDATE t_a SET bool1=temp WHERE int1=NEW.int1;
		END IF;
	END IF;
	RETURN NULL;
END;
$sei$ LANGUAGE plpgsql;

CREATE INDEX t_a_str1 ON t_a USING btree (str1) TABLESPACE ext;
CREATE INDEX str2_index ON t_b(str2);
CREATE INDEX t_b_int1_index ON t_b(int1);
CREATE INDEX t_c_str1_idx ON t_c(str1) TABLESPACE ext;
CREATE INDEX t_c_str2_idx ON t_c(str2) TABLESPACE ext;
ALTER INDEX t_c_pkey SET TABLESPACE ext;
ALTER INDEX ifl_pkey SET TABLESPACE ext;