db-schema-sys.md

EDR sys Schema Documentation

Overview

The sys schema holds tables, views and functions used throughout the database. These support: database documentation; essential operations relating to user identity, authentication and authorisation; and the generation of resource identifiers.

Figure sys schema tables.

Tables and views

sys.doc_column [view]

Documentation of columns on EDR database tables.

Column	Data type	Definition
schema_name	name	The name of the database schema that owns the table/column.
table_name	name	The name of the table that owns the column.
column_name	name	The name of the column.
key	text	Flags whether the column is part of a primary key (PK), foreign key (FK), or both (PK/FK).
fk_references	text	The path to the foreign table column referred to by the column if it is part of a foreign key.
data_type	character varying	The column data type.
is_nullable	character varying	Flags whether null values are allowed.
column_default	text	The default value for new records. May be a function (e.g. current user), or a constant value.
column_description	text	The description of the column.

sys.doc_constraint [view]

Documentation of check and unique constraints applied to EDR database tables.

Column	Data type	Definition
schema_name	name	The name of the database schema that owns the table/constraint.
table_name	name	The name of the table that owns the constraint.
constraint_name	name	The name of the constraint.
constraint_type	text	The type of constraint (check or unique).
constraint_columns	text	The columns governed by the constraint.
constraint_description	text	The description of the constraint.

sys.doc_function [view]

Documentation of EDR database functions.

Column	Data type	Definition
schema_name	name	The name of the database schema that owns the function.
function_name	name	The name of the constraint.
function_type	text	The type of function (function or procedure).
function_arguments	text	The arguments taken by the function.
function_returns	text	The return type of the function.
function_description	text	The description of the function.

sys.doc_index [view]

Documentation of indexes applied to EDR database tables.

Column	Data type	Definition
schema_name	name	The name of the database schema that owns the table/index.
table_name	name	The name of the table that owns the index.
index_name	name	The name of the index.
index_type	text	The type of index.
index_columns	text	The indexed columns.

sys.doc_schema [view]

Documentation of EDR defined database schemata.

Column	Data type	Definition
schema_name	name	The name of the database schema.
schema_description	text	The description of the database schema.

sys.doc_schema_markdown [view]

Documentation of EDR database schema and their contents, formatted using markdown.

Column	Data type	Definition
schema_name	name	The name of the database schema.
doc_markdown	text	The markdown document.

sys.doc_table [view]

Documentation of EDR database tables.

Column	Data type	Definition
schema_name	name	The name of the database schema that owns the table.
table_name	name	The name of the table.
table_type	text	The type of the table. E.g. table or view.
table_description	text	The description of the table.

Functions

Function	Type	Arguments	Returns	Description
array_remove_overlap	function	_target_array; _comparison_array	anyarray	Returns any overlapping elements in the comparison array from the target array.