custom_function.ex

# What the app uses... sad how we can't pass params the normal way since we're calling this custom function.
def estimated_count(query) do
  {raw_query_sql, vars} = Ecto.Adapters.SQL.to_sql(:all, OpenFn.Repo, query)
  |> IO.inspect

  est_count_sql = "SELECT count_estimate('#{raw_query_sql}', #{vars})"

  IO.inspect raw_query_sql
  IO.inspect vars

  Ecto.Adapters.SQL.query!(OpenFn.Repo, est_count_sql)
  |> Map.get(:rows)
  |> List.first
  |> IO.inspect
end

# The db migration to create a custom function
defmodule OpenFn.Repo.Migrations.CreateNewCountEstimateFunction do
  use Ecto.Migration

  def up do
    execute """
      CREATE OR REPLACE FUNCTION count_estimate(query text, bar text) RETURNS INTEGER AS
      $func$
      DECLARE
          rec   record;
          ROWS  INTEGER;
      BEGIN
          FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
              ROWS := SUBSTRING(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');
              EXIT WHEN ROWS IS NOT NULL;
          END LOOP;

          RETURN ROWS;
      END
      $func$ LANGUAGE plpgsql;
    """
  end

  def down do
    execute "DROP FUNCTION count_estimate(query text, bar text)"
  end
end