1 files changed, 19 insertions, 0 deletions
diff --git a/lib/Dakkar/TweetArchive/Store.pm b/lib/Dakkar/TweetArchive/Store.pm
index b6ce585..9a97ca6 100644
--- a/lib/Dakkar/TweetArchive/Store.pm
+++ b/lib/Dakkar/TweetArchive/Store.pm
@@ -341,4 +341,23 @@ CREATE TRIGGER tweets_fts_update
 SQL
 }
 
+sub _schema_deploy_5($self) {
+    my $dbh = $self->dbh;
+
+    # get *more* text of a tweet
+    $dbh->do(<<'SQL');
+CREATE OR REPLACE FUNCTION tweet_text(IN t JSONB) RETURNS text
+LANGUAGE sql
+IMMUTABLE
+RETURNS NULL ON NULL INPUT
+PARALLEL SAFE
+AS $$ SELECT
+CASE t ? 'extended_tweet'
+ WHEN true THEN COALESCE(t->'extended_tweet'->>'text',t->'extended_tweet'->>'full_text')
+ ELSE COALESCE(t->>'text',t->>'full_text')
+END
+$$;
+SQL
+}
+
 1;