Postgres VALUES in query

Questions / Help
2

Ecto doesn’t support such queries atm, at least not with runtime values. Though I’m wondering if you could join a jsonb_to_recordset, which would allow you to pass the list of values as a single parameterized list of values:

@doc """
  Use runtime data as an adhoc db table to join to data.

  `types` need to be a compile time string literal.
  `values` can be runtime supplied.
  
  ## Example
  
      data = [%{id: 1, text: "hey"}, %{id: 2, text: "ho"}]
      from a in Table,
        join: b in jsonb_recordset("id uuid, text text", data),
        on: a.id == b.id
        
  """
  defmacro jsonb_recordset(types, values) do
    quote do
      fragment(
        unquote("(SELECT * FROM json_to_recordset(?) as t(#{types}))"),
        ^unquote(values)
      )
    end
  end
3 Likes