is_parent_record_referenced(p_table_schema text, p_table_name text, p_parent_record_id uuid, p_excluded_relations regclass[] DEFAULT '{}'::regclass[])

Tests if a specific parent record is referenced in a foreign key relationship.

Parameters

• p_table_schema :: Required? True; Default: ( No Default )

  The name of the schema which hosts the parent table.

• p_table_name :: Required? True; Default: ( No Default )

  The name of the parent table.

• p_parent_record_id :: Required? True; Default: ( No Default )

  The ID value of the parent record which will be searched for in the child tables.

• p_excluded_relations :: Required? False; Default: No excluded relations

  An optional array of child table regclasses which should not be searched.

General Usage

The parent table is identified by the p_table_schema and p_table_name parameters and the specific record is identified using the p_parent_record_id parameter. The p_parent_record_id is expected to be the id column value of the parent relation.

On execution, the function will then look up all relations (children) with foreign key references to the parent table and its id column and test if the p_parent_record_id value exists in the child relation. Once a child relation with a reference is found, then the function returns TRUE and the search stops. If no child relations have a reference to the given parent record, then the function returns false.

There are several assumptions to be aware of when making use of this function. The function assumes that any foreign key reference to the parent relation is a simple, single column relationship to the parent relation’s id column; an assumption is made that constraint names are unique across the application, but there is no guarantee from PostgreSQL that this is true; and that by using this function the developer is aware that when there are either many child relations and/or the child relations contain many records, this function may not perform well, especially in cases where there are no references to the given parent record. Finally, if any of the parameter values are NULL, the function will return NULL.