\c template1 pgsql
DROP DATABASE test;
CREATE DATABASE test WITH OWNER nxadba;

\c test pgsql
CREATE FUNCTION public.plpgsql_call_handler () RETURNS language_handler
	AS '$libdir/plpgsql', 'plpgsql_call_handler'
	LANGUAGE c;

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER public.plpgsql_call_handler;

\c test nxadba
BEGIN;
CREATE SCHEMA s AUTHORIZATION nxadba;
REVOKE ALL ON SCHEMA s FROM PUBLIC;

CREATE TABLE s.a (
	z BIGINT NOT NULL,
	x BIGINT NOT NULL
);
CREATE UNIQUE INDEX a_udx ON s.a (z,x);

CREATE TABLE s.b (
	z BIGINT NOT NULL,
	y BIGINT NOT NULL
);
CREATE UNIQUE INDEX b_udx ON s.b (z,y);

CREATE TABLE s.c (
	x BIGINT NOT NULL,
	y BIGINT NOT NULL,
	w INT NOT NULL DEFAULT 1::INT
);
CREATE UNIQUE INDEX c_udx ON s.c (x,y);

CREATE FUNCTION s.add_y_to_x(BIGINT, BIGINT)
	RETURNS BOOL
	EXTERNAL SECURITY DEFINER
	AS '
DECLARE
	a_y ALIAS FOR $1;
	a_x ALIAS FOR $2;
	r_a s.a%ROWTYPE; --  RECORD;
	v_w s.c.w%TYPE;
BEGIN
	SELECT d.w INTO v_w FROM s.c d WHERE d.x = a_x AND d.y = a_y;
	IF FOUND THEN
		UPDATE s.c SET w = (v_w + 1::INT) WHERE x = a_x AND y = a_y;
	ELSE
		INSERT INTO s.c (x,y,w) VALUES (a_x,a_y,1::INT);
	END IF;

	FOR r_a IN SELECT d.z, d.x FROM s.a d WHERE d.x = a_x LOOP
		RAISE INFO ''a_y: %, r_a.z: %'', a_y, r_a.z;
	END LOOP;

	RETURN TRUE;
END;

' LANGUAGE 'plpgsql';

CREATE FUNCTION s.a_ins()
	RETURNS TRIGGER
	EXTERNAL SECURITY DEFINER
	AS '
DECLARE
	r_c s.c%ROWTYPE; -- RECORD;
BEGIN
	FOR r_c IN SELECT d.y FROM s.c d WHERE d.x = NEW.x LOOP
		PERFORM s.add_y_to_x(r_c.y,NEW.z);
	END LOOP;

	RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER a_ins BEFORE INSERT ON s.a FOR EACH ROW
        EXECUTE PROCEDURE s.a_ins();

CREATE FUNCTION s.b_ins()
	RETURNS TRIGGER
	EXTERNAL SECURITY DEFINER
	AS '
BEGIN
	PERFORM s.add_y_to_x(NEW.y, NEW.z);

	RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER b_ins BEFORE INSERT ON s.b FOR EACH ROW
        EXECUTE PROCEDURE s.b_ins();

COMMIT;

\c test pgsql
VACUUM FULL FREEZE ANALYZE;


\c test nxadba
SET search_path = s;

BEGIN;
INSERT INTO s.b (z,y) VALUES (1,1);
INSERT INTO s.b (z,y) VALUES (1,2);
INSERT INTO s.b (z,y) VALUES (1,3);
INSERT INTO s.b (z,y) VALUES (2,4);
INSERT INTO s.b (z,y) VALUES (2,5);
INSERT INTO s.b (z,y) VALUES (2,6);
INSERT INTO s.b (z,y) VALUES (3,7);
COMMIT;

INSERT INTO s.a (z,x) VALUES (4,2);
INSERT INTO s.a (z,x) VALUES (4,3);
INSERT INTO s.a (z,x) VALUES (5,4);