Delete data from custom table when deleting a post

delete_post fires when the post is deleted permanently, not when it is trashed. I don’t know if that is relevant but keep that in mind. That is the only real WordPress specific part of this question. The rest is bad PHP.

Variables do not expand inside single quotes. You are a sending a query to the database that literally looks like

SELECT `id` FROM $table_name WHERE `pid` = XxX

What you want is either

$wpdb->prepare('SELECT `id` FROM '.$table_name.' WHERE `pid` = %d', $post_id)

Or

$wpdb->prepare("SELECT `id` FROM {$table_name} WHERE `pid` = %d", $post_id)

To run the function when you trash the post, you probably want the one or more of the status transition hooks. But a word or caution: if you delete from your table on post “trash” you can’t reverse it. The “restore” function will fail to work correctly on these posts.