# MergeIntoPolyfill
Features:
* provide a DSL to express postgres [`MERGE` queries](https://www.postgresql.org/docs/current/sql-merge.html)
* depending on the current postgres version,
* `>= 15` runs the query as a single `MERGE` query
* `< 15` will run a more involved plan in a transaction:
* select query to find the matches specified
* one insert/update/delete query for each match
* both ways have the same effect, but the `MERGE` query is more performant,
since the parameters have to be sent only once.
## Examples
The following is code from the test:
```elixir
Repo.insert(%Book{title: "Book 2", year: 1999})
Repo.insert(%Book{title: "Book 10", year: 2007})
Repo.insert(%Book{title: "Book 3", year: 2000})
source_query =
from(gs in fragment("generate_series(1, 10)"),
select: %{
id: gs + 0,
title: fragment("concat(?::text, ?)", ^"Book ", gs),
year: gs + 2000
}
)
merge_into(Book, as(:target).title == as(:source).title, source_query) do
matched?() and as(:source).year >= 2008 ->
update([:year])
matched?() and as(:target).title == ^"Book 2" ->
delete()
matched?() ->
update(title: fragment("concat(?, ' (', ?, ')')", as(:target).title, as(:source).year))
not matched?() ->
insert([:title, :year])
end
|> Repo.transaction()
```
For Postgres 15 and newer, it will run this query:
```sql
MERGE INTO "books" AS b0
USING (SELECT
sf0 + 0 AS "id",
concat($1::text, sf0) AS "title",
sf0 + 2000 AS "year"
FROM generate_series(1, 10) AS sf0
) AS s1
ON b0."title" = s1."title"
WHEN MATCHED AND s1."year" >= 2008
THEN UPDATE SET "year" = s1."year"
WHEN MATCHED AND b0."title" = $2
THEN DELETE
WHEN MATCHED AND TRUE
THEN UPDATE SET "title" = concat(b0."title", ' (', s1."year", ')')
WHEN NOT MATCHED AND TRUE
THEN INSERT ("title", "year") VALUES (s1."title", s1."year");
-- ["Book ", "Book 2"]
```
For anything older than Postgres 15, these queries will be executed:
```sql
begin;
SELECT jsonb_set(jsonb_set(jsonb_set(jsonb_set(jsonb_build_object('target_id', b0."id", 'source_id', s1."id")::jsonb, $1::text[], to_jsonb(NOT (b0."id" IS NULL) AND (s1."year" >= 2008)::boolean))::jsonb, $2::text[], to_jsonb(NOT (b0."id" IS NULL) AND (b0."title" = $3)::boolean))::jsonb, $4::text[], to_jsonb(NOT (b0."id" IS NULL) AND TRUE::boolean))::jsonb, $5::text[], to_jsonb((b0."id" IS NULL) AND TRUE::boolean)) FROM "books" AS b0 RIGHT OUTER JOIN (SELECT sf0 + 0 AS "id", concat($6::text, sf0) AS "title", sf0 + 2000 AS "year" FROM generate_series(1, 10) AS sf0) AS s1 ON b0."title" = s1."title";
-- [["0"], ["1"], "Book 2", ["2"], ["3"], "Book "]
UPDATE "books" AS b0 SET "year" = s1."year" FROM (SELECT sf0 + 0 AS "id", concat($1::text, sf0) AS "title", sf0 + 2000 AS "year" FROM generate_series(1, 10) AS sf0) AS s1 WHERE (b0."title" = s1."title") AND (b0."id" = ANY($2));
-- ["Book ", [455]]
DELETE FROM "books" AS b0 WHERE (b0."id" = ANY($1));
-- [[454]]
UPDATE "books" AS b0 SET "title" = concat(b0."title", ' (', s1."year", ')') FROM (SELECT sf0 + 0 AS "id", concat($1::text, sf0) AS "title", sf0 + 2000 AS "year" FROM generate_series(1, 10) AS sf0) AS s1 WHERE (b0."title" = s1."title") AND (b0."id" = ANY($2));
-- ["Book ", [456]]
INSERT INTO "books" ("title","year") (SELECT s0."title", s0."year" FROM (SELECT sf0 + 0 AS "id", concat($1::text, sf0) AS "title", sf0 + 2000 AS "year" FROM generate_series(1, 10) AS sf0) AS s0 WHERE (s0."id" = ANY($2)));
-- ["Book ", [5, 7, 4, 9, 8, 6, 1]]
commit;
```
## Setup
1. Add the dependency to your `mix.exs`:
```elixir
{:merge_into_polyfill, "~> 0.1"}
```
2. Add `MergeIntoPolyfill.CheckVersion` to your application supervisor, directly after the ecto repo is started:
```elixir
children = [
# ...
MyApp.Repo,
{MergeIntoPolyfill.CheckVersion, MyApp.Repo}
# ...
]
```
Alternatively, you can set the desired builder every time you are using the `merge_into` macro:
```elixir
merge_into(Schema, as(:source).id == as(:target).id, values, builder: MergeIntoPolyfill.Builders.MergeInto) do
# ...
end
```
or by updating the env that is set by `MergeIntoPolyfill.CheckVersion`:
```elixir
Application.put_env(:merge_into_polyfill, MergeIntoPolyfill.CheckVersion, builder: MergeIntoPolyfill.Builders.Polyfill)
```
Done! The `MERGE INTO` polyfill is now ready to be used.