PostgreSQL - Remove tuple referenced by another table

You have set up a function for this:

CREATE LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION checkDependence(VARCHAR, VARCHAR, INTEGER) RETURNS BOOLEAN AS $$
DECLARE 
    tableName   ALIAS FOR $1;
    columnName  ALIAS FOR $2;
    id          ALIAS FOR $3;

    sqlExecute  RECORD;
    sqlExecute2 RECORD;
    sqlQuery    VARCHAR;
    sqlQuery2   VARCHAR;
    primaryKey  VARCHAR;
BEGIN

    sqlQuery := 'SELECT table_name';
    sqlQuery := sqlQuery || ' FROM information_schema.columns';
    sqlQuery := sqlQuery || ' WHERE table_name != '''||tableName||'''';
    sqlQuery := sqlQuery || ' AND column_name = '''||columnName||'''';

    FOR sqlExecute IN EXECUTE(sqlQuery) LOOP

        RAISE NOTICE '%', sqlExecute.table_name;
        RAISE NOTICE '%', columnName;
        RAISE NOTICE '%', id;

        sqlQuery2 := 'SELECT CASE WHEN count(1) > 0 THEN TRUE ELSE FALSE END AS has';
        sqlQuery2 := sqlQuery2 || ' FROM '||sqlExecute.table_name;
        sqlQuery2 := sqlQuery2 || ' WHERE '||columnName||' = '||id;

        FOR sqlExecute2 IN EXECUTE(sqlQuery2) LOOP
            RAISE NOTICE '%', sqlExecute2;
            IF sqlExecute2.has THEN
                RETURN TRUE;
            END IF;
        END LOOP;
    END LOOP;

    RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

Now you only have to call it in your select:

SELECT
    checkDependence('table_pai', 'id', id);

Making count can be slow. Solution with exists :

select *,
    not (
        exists (
            select 1
            from telemetria
            where id_sistema = s.id
        ) or
        exists (
            select 1
            from alerta
            where id_sistema = s.id
        ) or
        exists (
            select 1
            from controle
            where id_sistema = s.id
        ) or
        exists (
            select 1
            from cliente_sistema
            where id_sistema = s.id
        )
    ) as pode_deletar
from sistema s

If you get good performance it can also be turned into function

If the rows of the child tables you are checking are not important, I mean, if you only need to check for daughters that reference the parent you are trying to delete, you can use something like:

SELECT 1 FROM tbl_filha1 WHERE id_pai = <id do pai>;

[...]

SELECT 1 FROM tbl_filhaN WHERE id_pai = <id do pai>;

Note that you will have to do this for the N child tables (at least until you find a result) which would be a bit onerous. Considering this, in its place I would do the routine for deletion from the parent table and treat the error in the application if the DBMS bars the deletion, without doing previous checking of child tables.

Try the following:

SELECT s.*, 

CASE WHEN 
    t.id_sistema IS NOT NULL OR
    c.id_sistema IS NOT NULL OR
    cs.id_sistema IS NOT NULL 
    THEN 'disabled'

END as desabilitar 

FROM sistema s
    LEFT JOIN telemetria t ON s.id = t.id_sistema
    LEFT JOIN controle c ON s.id = c.id_sistema
    LEFT JOIN cliente_sistema cs ON s.id = cs.id_sistema

The code above will list all your systems making a left join with the other tables. This will cause all systems to be listed independently if the system_id exists in the other tables.

The WHEN CASE is checking if system_id in the other tables is not null. If it is not null, it creates the disable column = 'disabled'.

Now in your HTML, in the system list, use the disable field to assign the disabled button.

<button <?= $rs['desabilitar'] ?>>DELETAR</button>

If you are using link tag, change the value of the disable column to 'none', and then hide the link with an inline css:

<a style="display:<?=$rs['desabilitar']?>">

This is to avoid IF . But you can return true and make an if checking if disabling = true, hides the button.

But since you said that there are millions of records, perhaps the best option is to treat the bank exception when you attempt to delete.

But worth trying this solution =)

I hope I have helped!