BEGIN;


DROP SCHEMA IF EXISTS test_join CASCADE;

CREATE SCHEMA test_join;

CREATE TABLE test_join.jointest_a (id serial NOT NULL, fkey smallint, geometry geometry(Point,21781), CONSTRAINT jointest_a_pkey PRIMARY KEY (id));
CREATE TABLE test_join.jointest_b (id serial NOT NULL, fkey smallint,                                 CONSTRAINT jointest_b_pkey PRIMARY KEY (id));
CREATE TABLE test_join.jointest_c (id serial NOT NULL, fkey smallint,                                 CONSTRAINT jointest_c_pkey PRIMARY KEY (id));
CREATE TABLE test_join.jointest_d (id serial NOT NULL, fkey smallint,                                 CONSTRAINT jointest_d_pkey PRIMARY KEY (id));
CREATE TABLE test_join.jointest_e (id serial NOT NULL,                value varchar(30),              CONSTRAINT jointest_e_pkey PRIMARY KEY (id));

CREATE OR REPLACE FUNCTION test_join.get_random_coord() RETURNS geometry(Point,21781) AS $$
DECLARE
    min_x integer := 147650;
    min_y integer := 556940;
    max_x integer := 141510;
    max_y integer := 563980;
BEGIN
	RETURN ST_SetSRID(ST_MakePoint(
		trunc(random() * (max_x-min_x) + min_x),
		trunc(random() * (max_y-min_y) + min_y)	
	),21781);
     
END;
$$ LANGUAGE 'plpgsql' STRICT;


DO
	$do$
	BEGIN 
	FOR i IN 1..100 LOOP
	   INSERT INTO test_join.jointest_a (fkey,geometry) VALUES (i,test_join.get_random_coord()) ;
	   INSERT INTO test_join.jointest_b (fkey) VALUES (i);
	   INSERT INTO test_join.jointest_c (fkey) VALUES (i);
	   INSERT INTO test_join.jointest_d (fkey) VALUES (i);
	   INSERT INTO test_join.jointest_e (value) VALUES (i::varchar);
	END LOOP;
	END
	$do$
	;
COMMIT;
