CREATE TYPE type_check_foreign_key AS (
	fk_name text,
	main_schema text,
	main_table text,
	main_key text,
	constraint_delete text,
	constraint_update text,
	fk_schema text,
	fk_table text,
	fk_key text
);


--
-- Name: get_all_foreign_keys_by_pg_catalog(); Type: FUNCTION; Schema: rpl_metadata; Owner: -
--

CREATE FUNCTION get_all_foreign_keys_by_pg_catalog() RETURNS SETOF type_check_foreign_key
    LANGUAGE plpgsql STABLE
    AS $$
DECLARE 
	query		TEXT;
	oRow		RECORD;
	retRow		rpl_metadata.type_check_foreign_key;
	i		integer;
	oRowAttr	RECORD;
BEGIN
	query :='SELECT 
		t1.conname AS fk_name
		,t1.confupdtype AS update_
		,t1.confdeltype AS delete_
		,f_nsp.nspname AS fk_schema
		,f_tab.relname AS fk_table
		,f_tab.oid AS f_tabid
		,t1.conkey AS fk_key_arr
		,array_upper(t1.conkey,1) AS arr_size
		,p_nsp.nspname AS pk_schema
		,p_tab.relname aS pk_table
		,p_tab.oid AS p_tabid
		,t1.confkey AS main_key_arr
	FROM 
		pg_constraint AS t1 
		JOIN pg_class AS f_tab ON (t1.conrelid = f_tab.oid)
		JOIN pg_namespace AS f_nsp ON (f_tab.relnamespace=f_nsp.oid)
		JOIN pg_class AS p_tab ON (t1.confrelid = p_tab.oid)
		JOIN pg_namespace AS p_nsp ON (p_tab.relnamespace=p_nsp.oid)
	WHERE
		t1.contype = ''f''
	';
--	RAISE NOTICE 'query - %',query;	
	
	FOR oRow IN EXECUTE query LOOP
		retRow.fk_name :=oRow.fk_name;
		retRow.main_schema :=oRow.pk_schema;
		retRow.main_table :=oRow.pk_table;
		retRow.fk_schema :=oRow.fk_schema;
		retRow.fk_table :=oRow.fk_table;
		retRow.constraint_delete :=oRow.update_;
		retRow.constraint_update :=oRow.delete_;

		FOR i IN 1..oRow.arr_size LOOP
			query :='
				SELECT 
					f_attr.attnum AS fk_num
					,f_attr.attname AS fk_key
					,p_attr.attnum AS main_num
					,p_attr.attname AS main_key
				FROM 
					pg_attribute AS f_attr,
					pg_attribute AS p_attr
				WHERE
					f_attr.attnum='||oRow.fk_key_arr[i]||'
					AND f_attr.attrelid='||oRow.f_tabid||'
					AND p_attr.attnum='||oRow.main_key_arr[i]||'
					AND p_attr.attrelid='||oRow.p_tabid||'
				;					
				';
--			RAISE NOTICE 'query - %',query;	
			FOR oRowAttr IN EXECUTE query LOOP
				retRow.main_key :=oRowAttr.main_key;	
				retRow.fk_key :=oRowAttr.fk_key;	
				RETURN NEXT retRow;
			END LOOP;	
			
		END LOOP;	
		
	END LOOP;
	
	RETURN;
	
END;
$$;


--
-- Name: FUNCTION get_all_foreign_keys_by_pg_catalog(); Type: COMMENT; Schema: rpl_metadata; Owner: -
--

COMMENT ON FUNCTION get_all_foreign_keys_by_pg_catalog() IS '
 * Функция возвращает наборы ключей (foreign key) из системных таблиц
 * Если ключ составной то его можно взять по имени таблицы и его наименованию
 * Просто по наименованию нельзя наименования уникальны только в рамках одной схемы !!!
 * @Версия: 1.1
';

SELECT * FROM information_schema.tables; -- таблицы
SELECT * FROM information_schema.columns; -- колонки