From a545c6e1e68ffad18853eeee9868dfafa60a3c23 Mon Sep 17 00:00:00 2001
From: Maksim Pechnikov <parallel588@gmail.com>
Date: Tue, 4 Aug 2020 12:00:51 +0300
Subject: added index

---
 ...ttachment_urls_function_and_index_to_object.exs | 37 ++++++++++++++++++++++
 1 file changed, 37 insertions(+)
 create mode 100644 priv/repo/migrations/20200804055848_add_object_attachment_urls_function_and_index_to_object.exs

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
-- 
cgit v1.2.3