Query all columns in a schema with Ecto

Questions / Help
,
1

Is there a way to do a wildcard query across all tables in any Ecto schema postgres? Or is there some kind of search library I could use to do something like this?

2

https://www.postgresql.org/docs/current/infoschema-columns.htmlYou can use Information_schema. columns to get information about all tables and columns in the data base.

Ecto.Adapters.SQL.query/4 lets you do custom SQL queries.

https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#query/4

Hope that is somewhat helpful.

3

Here’s an example:

  def many_generic(table_name) when is_binary(table_name) do
    from(m in table_name)
    |> select(^table_fields(table_name))
    |> repo().many()
  end

  # note: in our codebase the result of this function is cached so subsequent calls don't query it again
  defp table_fields(table) when is_binary(table) do
    with rows <-
           repo().many(
             from "columns",
               prefix: "information_schema",
               select: [:column_name],
               where: [table_name: ^table]
           ) do
      for row <- rows do
        # naughty but limited to existing DB fields
        String.to_atom(row[:column_name])
      end
    end
  end
1 Like