[ericHosick]

To help understand why a complex query with many joins comes back empty, I like to provide helper debug functions that builds the query step by step. Each block checks one prerequisite and tells the user what’s missing:

```sql FUNCTION debug_user(user_id):

  IF NOT (SELECT FROM user WHERE user.user_id = user_id) THEN
    -- user doesn’t exist
  END IF;

  IF NOT (SELECT FROM user
           JOIN user_addr ON user_addr.user_id = user.user_id
           WHERE user.user_id = user_id) THEN
    -- user has no address
  END IF;

  -- keep adding joins until you find the break

```

because when you have a query that involves 6 relations, and you don't get results, it is nice to know why.

[FrancoisBosun]

I do the "same thing", but using PostgreSQL `EXPLAIN ANALYZE`. EXPLAIN ANALYZE has information about the number of rows returned, which means I know exactly which node in the query plan failed to return expected values.

[ericHosick]

don't mean to push the goalpost (didn't mention the following in the above post)

these functions can also be exposed in admin ui's making it easier for the general admin users/support users.

another very useful use case is when row level security (RLS) is enabled.

[polishdude20]

Not for function though unless you specify it as a debug flag inside the function?

[iTokio]

You can can also run a detailed explain plan and with a suitable visualizer it’s trivial to see where the query fell apart. You get to see the actual data distribution this way.