added index

1 files changed, 37 insertions, 0 deletions

diff --git a/priv/repo/migrations/20200804055848_add_object_attachment_urls_function_and_index_to_object.exs b/priv/repo/migrations/20200804055848_add_object_attachment_urls_function_and_index_to_object.exs
new file mode 100644
index 000000000..9536ad9f1
--- /dev/null
+++ b/priv/repo/migrations/20200804055848_add_object_attachment_urls_function_and_index_to_object.exs
@@ -0,0 +1,37 @@
+defmodule Pleroma.Repo.Migrations.AddObjectAttachmentUrlsFunctionAndIndexToObject do
+  use Ecto.Migration
+
+  @disable_ddl_transaction true
+  @disable_migration_lock true
+
+  def up do
+    """
+    CREATE OR REPLACE FUNCTION object_attachment_urls(j jsonb)
+    RETURNS text[] AS $$
+    BEGIN
+    RETURN ARRAY(
+        SELECT elem->> 'href'
+        FROM jsonb_array_elements(j #> '{url}') elem
+        WHERE jsonb_typeof(j::jsonb #> '{url}') = 'array'
+    );
+    END;
+    $$ LANGUAGE plpgsql IMMUTABLE;
+    """
+    |> execute()
+
+    create(
+      index(:objects, ["object_attachment_urls(data)"],
+        name: :object_attachment_urls_index,
+        concurrently: true
+      )
+    )
+  end
+
+  def down do
+    drop_if_exists(
+      index(:objects, ["object_attachment_urls(data)"], name: :object_attachment_urls_index)
+    )
+
+    execute("drop function if exists object_attachment_urls(j jsonb)")
+  end
+end