Ecto raw sql with single result in a quick way

As an example I'm using the following right now.

query = """
  SELECT reltuples
  AS approximate_row_count
  FROM pg_class
  WHERE relname = 'table';
"""

query
|> Repo.query!()
|> Map.get(:rows)
|> List.flatten()
|> List.first()
|> trunc()

It executes the query, receives the result

%Postgrex.Result{
  columns: ["approximate_row_count"],
  command: :select,
  connection_id: 17152,
  messages: [],
  num_rows: 1,
  rows: [[1494644.0]]
}

selects the rows [[1494644.0]]

flattens the list [1494644.0]

gets the first entry 1494644.0

and converts it to an integer 1494644

I feel like apart form the last step, there has to be a shorter way for this. But I have not found it yet. Please note, that I'm not looking for a way to execute that simple query as an ecto query, but rather as raw sql. Because I use a lot more complicated queries with the same issue.

Solution

I would go with pattern matching.

with [[result]] <- Repo.query!(query).rows,
  do: trunc(result)

This would return all the rows from the result back if there is more than one row returned. To fully mimic the original behavior (List.first/1 which is essentially the same as hd/1), use:

with [[result|_]|_] <- Repo.query!(query).rows,
  do: trunc(result)

Also, one might gracefully handle errors with else: clause.