-- Function: slony_generate_set_adds(integer, text, integer)

-- DROP FUNCTION slony_generate_set_adds(integer, text, integer);

CREATE OR REPLACE FUNCTION slony_generate_set_adds(integer, text, integer)
  RETURNS void AS
$BODY$

DECLARE

p_set_id	ALIAS FOR $1;
p_schema	ALIAS FOR $2;
p_start_seq 	ALIAS FOR $3;

v_record	record;
v_schema	TEXT;
v_relname	TEXT;
v_seq		TEXT;
v_prefix_digits	INT4;
v_ctr		INT4;

v_msg		TEXT;

BEGIN

v_prefix_digits := 3;
v_ctr 		:= p_start_seq;

CREATE TEMP TABLE t_slony_set_add
( slonik_command TEXT )
ON COMMIT DROP;

--SELECT p_set_id::text || LPAD(v_ctr::text, v_prefix_digits, '0') INTO v_seq;
SELECT v_ctr INTO v_seq;

-- 2008-06-12
-- Sorting by OID replaced by sort by FK count

FOR v_record IN SELECT n.nspname, t.relname , 0 as count
                  FROM pg_class t
                  JOIN pg_namespace n ON n.oid = t.relnamespace
                 WHERE relkind = 'r'
                   AND relname NOT LIKE 'pg_%'
                   AND relname NOT LIKE 'sql_%'
                   AND n.nspname = p_schema
                   AND t.OID NOT IN 
                            (SELECT conrelid
                               FROM pg_constraint
                              WHERE contype = 'f'
                                AND contype <> 'p'
                                AND contype <> 'c')
                UNION
               SELECT n.nspname, t.relname as table, count(c.conname) as count
                 FROM pg_class t
                 JOIN pg_constraint c ON ( c.conrelid = t.OID AND c.contype = 'f')
                 JOIN pg_namespace n ON n.oid = t.relnamespace
                WHERE relkind = 'r'
                  AND t.relname NOT LIKE 'pg_%'
                  AND t.relname NOT LIKE 'sql_%'
                  AND n.nspname != 'rollback'
                  AND CASE WHEN p_schema IS NOT NULL 
			THEN n.nspname = p_schema
			ELSE TRUE
			END
            GROUP BY n.nspname, t.relname
            ORDER BY 3, 2

LOOP

SELECT 'SET ADD TABLE (SET ID=' || p_set_id || ', ORIGIN=1, ID=' 
	|| v_seq || ', FULLY QUALIFIED NAME=' || ''''
	|| v_record.nspname || '.' || v_record.relname || '''' || ', comment=' || '''' 
	|| v_record.relname || ' fks->'|| v_record.count::text || ''');' INTO v_msg;

INSERT INTO t_slony_set_add 
( slonik_command )
VALUES
( v_msg);

RAISE NOTICE '%', v_msg;

	v_ctr := v_ctr +1;
	--SELECT p_set_id::text || LPAD(v_ctr::text, v_prefix_digits, '0') INTO v_seq;
	SELECT v_ctr INTO v_seq;

END LOOP;

v_prefix_digits := v_prefix_digits + 1;
--v_ctr 		:= 1;
v_ctr 		:= p_start_seq;

--SELECT p_set_id::text || LPAD(v_ctr::text, v_prefix_digits, '0') INTO v_seq;
SELECT v_ctr INTO v_seq;

FOR v_record IN SELECT n.nspname, c.relname 
	          FROM pg_class c
	     LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
	     LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
	         WHERE c.relkind = 'S'::"char"
		   AND CASE WHEN p_schema IS NOT NULL 
			THEN n.nspname = p_schema
			ELSE TRUE
			END
	      ORDER BY c.oid

LOOP

SELECT 'SET ADD SEQUENCE (SET ID=' || p_set_id || ', ORIGIN=1, ID='
	|| v_seq || ', FULLY QUALIFIED NAME='
	|| '''' || v_record.nspname || '.'|| v_record.relname || '''' || ', comment='
	|| '''' || v_record.relname || ''');' INTO v_msg;

RAISE NOTICE '%', v_msg;
INSERT INTO t_slony_set_add 
( slonik_command )
VALUES
( v_msg);

v_ctr := v_ctr +1;

--SELECT p_set_id::text || LPAD(v_ctr::text, v_prefix_digits, '0') INTO v_seq;
SELECT v_ctr INTO v_seq;

END LOOP;

COPY t_slony_set_add 
--TO '/tmp/slony_set_adds.txt';
TO 'F:\temp\slony_set_adds.txt';

RETURN;
-- SAMPLE CALL
-- SELECT slony_generate_set_adds(1, 'public', 1)
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION slony_generate_set_adds(integer, text)
  OWNER TO postgres;