PostgreSQL Recursive Delete with Foreign Key Constraint

Question

In my PostgreSQL table, when I try to delete a record, I encounter the following error:

SQL Error [23503]: ERROR: update or delete on table "parent_table" violates foreign key constraint "parent_table_header_ref_id_id_4fd15d08_fk" on table "child_table"
  Detail: Key (id)=(1) is still referenced from table "child_table".

This happens even with ON DELETE RESTRICT enabled. Is there any script or a PostgreSQL function/loop that can be used to recursively delete data, ensuring that all related records across multiple levels are deleted safely when foreign key constraints are in place?

Python Pseudo code:

def delete_record(query):
    while True:
        try:
            cursor.execute(query)
            break
        except Exception as exception:
            # exception = 'SQL Error [23503]: ERROR: update or delete on table "parent_table" violates foreign key constraint "parent_table_header_ref_id_id_4fd15d08_fk" on table "child_table"'
            child_table_delete_query = get_delete_query(exception)
            # child_table_delete_query = 'delete from child_table where header_ref_id_id = 1'
            delete_record(child_table_delete_query)

delete_record('delete from parent_table where id = 1')

Answer 1

I found a solution to recursively delete PostgreSQL table data with a WHERE condition. While TRUNCATE TABLE <table_name> CASCADE; works, it's very risky as it lacks the functionality to specify a WHERE condition.

To address this, I wrote a Python script that recursively deletes table data with a WHERE condition and includes a feature to ask for confirmation before deleting the data.

Here's an example of how to use the script:

Please Type Delete Query: DELETE FROM table_name WHERE id = 13;

You can access the script here: recursive_delete.py

Feel free to contribute if you'd like to convert this into a PL/PGSQL script.