/**
 * Creates a view to get all foreign keys of the current database.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW pg_foreign_keys AS
SELECT ccu.constraint_name
	, tc.is_deferrable
	, tc.initially_deferred
	, tc."enforced"
	, tc.table_schema
	, tc.table_name
	, kcu.column_name
	, ccu.table_schema AS foreign_table_schema
	, ccu.TABLE_NAME AS foreign_table_name
	, ccu.COLUMN_NAME AS foreign_column_name
	, EXISTS
		(
			SELECT 1
			FROM pg_catalog.pg_index AS i
			WHERE i.indrelid = cs.conrelid
				AND i.indpred IS NULL
				AND (i.indkey::smallint[])[0:cardinality(cs.conkey)-1] OPERATOR(pg_catalog.@>) cs.conkey
		) AS is_indexed
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
	INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
		ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
	INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu
		ON ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
	INNER JOIN PG_CATALOG.PG_NAMESPACE AS n
		ON tc.table_schema = n.nspname
	INNER JOIN PG_CATALOG.PG_CONSTRAINT AS cs
		ON n."oid" = cs.connamespace
		AND tc.constraint_name = cs.conname
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
;

COMMENT ON VIEW pg_foreign_keys IS 'The view returns all foreign keys of the current database';
COMMENT ON COLUMN pg_foreign_keys.constraint_name IS 'The name of the constraint.';
COMMENT ON COLUMN pg_foreign_keys.is_deferrable IS 'Whether the setting can be changed within a transaction.';
COMMENT ON COLUMN pg_foreign_keys.initially_deferred IS 'Whether each row is checked on insert or at the end of the transaction.';
COMMENT ON COLUMN pg_foreign_keys.enforced IS 'For PostgreSQL versions <18 it is alway TRUE as the feature is not availabe, starting with PostgreSQL 18 it is TRUE, when the constraint is enforced.';
COMMENT ON COLUMN pg_foreign_keys.table_schema IS 'The name of the schema';
COMMENT ON COLUMN pg_foreign_keys.table_name IS 'The name of the table.';
COMMENT ON COLUMN pg_foreign_keys.column_name IS 'Name of the column used in the foreign key constraint.';
COMMENT ON COLUMN pg_foreign_keys.foreign_table_schema IS 'The name of the foreign schema.';
COMMENT ON COLUMN pg_foreign_keys.foreign_table_name IS 'The name of the foreign table.';
COMMENT ON COLUMN pg_foreign_keys.foreign_column_name IS 'Name of the column used in the foreign key constraint of a foreign table.';
COMMENT ON COLUMN pg_foreign_keys.is_indexed IS 'Whether an index exists for the foreign key constraint, or not.';