I'm trying to convert my FTS based search algorithm from SQLite to Postgres, but by reusing my old functions. It builds an index (setup_fts()) at the start of the thread and searches through the rows (of type text and bigint; I don't want to convert them).  Please provide the full grab.py (containing setup_fts() and the app route) and the full grab_search.py without long explanations.

def setup_fts():
    # Create the FTS table
    cursor.execute('''
    CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts USING fts5(
        id1 UNINDEXED,
		id2 UNINDEXED,
        board UNINDEXED,
        message,
        subject,
		files,
		date,
        tokenize = 'unicode61'
    );
    ''')
    connection.commit()

    # Clear existing data from the FTS table
    cursor.execute('DELETE FROM messages_fts;')
    connection.commit()

    boards = []

    cursor.execute("SELECT name FROM sqlite_schema WHERE type ='table' AND name NOT LIKE 'sqlite_%'")
    rows = cursor.fetchall()

    for row in rows:
        json_like_memoryview = row[0]
        json_like_bytes = bytes(json_like_memoryview)  # Convert memoryview to bytes
        json_like_string = json_like_bytes.decode('utf-8')  # Convert bytes to str
        boards.append(json_like_string)

    # Populate the FTS table
    for board in boards:
        if board != 'fadenWache' and "messages_fts" not in board:
            # Insert top-level messages and subjects
            cursor.execute(f'''
            INSERT INTO messages_fts (id1, board, message, subject, files, date)
            SELECT {board}.id AS id1,
                   '{board}' AS board,
                   json_extract({board}.voll, '$.message') AS message,
                   json_extract({board}.voll, '$.subject') AS subject,
				   json_extract({board}.voll, '$.files') AS files,
				   zeit as date
            FROM {board}
            WHERE json_valid({board}.voll)
              AND (json_extract({board}.voll, '$.message') IS NOT NULL
                   OR json_extract({board}.voll, '$.subject') IS NOT NULL);
            ''')

            # Insert messages and subjects from nested posts
            cursor.execute(f'''
            INSERT INTO messages_fts (id1, id2, board, message, subject, files, date)
            SELECT {board}.id AS id1,
			       json_extract(p.value, '$.postId') AS id2,
                   '{board}' AS board,
                   json_extract(p.value, '$.message') AS message,
                   json_extract(p.value, '$.subject') AS subject,
				   json_extract(p.value, '$.files') AS files,
				   zeit as date
            FROM {board}
            JOIN json_each({board}.voll, '$.posts') AS p
            WHERE json_valid({board}.voll)
              AND (json_extract(p.value, '$.message') IS NOT NULL
                   OR json_extract(p.value, '$.subject') IS NOT NULL);
            ''')
    connection.commit()

    # Set up triggers for each board
    for board in boards:
        if board != 'fadenWache':
            # Drop existing triggers if they exist
            cursor.executescript(f'''
            DROP TRIGGER IF EXISTS {board}_after_insert;
            DROP TRIGGER IF EXISTS {board}_after_update;
            DROP TRIGGER IF EXISTS {board}_after_delete;
            ''')

            # Create triggers (same as in step 3)
            # ... (Use the trigger creation code provided earlier)

    connection.commit()
    print('completed setup_fts')

@app.route('/search', methods=['GET'])
def doSearch():
	#bid=request.form['fbid']
	
	tic = time.perf_counter()
	search_term=request.args.get('q')
	
	cursor=connection.cursor()

	cursor.execute("SELECT name FROM sqlite_schema WHERE type ='table' AND name NOT LIKE 'sqlite_%'")
	rows = cursor.fetchall()
	
	boards = []
	
	for row in rows:
		json_like_memoryview = row[0]
		json_like_bytes = bytes(json_like_memoryview)  # Convert memoryview to bytes
		json_like_string = json_like_bytes.decode('utf-8')  # Convert bytes to str
		boards.append(json_like_string)

	search_term=" ".join(search_term.split())

	if not search_term:
		# Retrieve all records without using the MATCH clause
		query = '''
			SELECT id1, id2, board, message, subject, files, date
			FROM messages_fts;
		'''
		cursor.execute(query)
	else:
		# Use the MATCH clause as usual
		query = '''
			SELECT id1, id2, board, message, subject, files, date
			FROM messages_fts
			WHERE messages_fts MATCH ?;
		'''
		cursor.execute(query, (search_term,))


	results = cursor.fetchall()

	out=""
	outnum=0

	# Define the regex pattern to match HTML tags and text outside of tags
	pattern = r'(<[^>]*>)|([^<]+)'

	# Determine if 'search_term' is enclosed in quotation marks
	if (search_term.startswith('"') and search_term.endswith('"')) or (search_term.startswith("'") and search_term.endswith("'")):
		is_quoted = True
		# Remove quotation marks
		search_term_clean = search_term[1:-1].strip()
	else:
		is_quoted = False
		# Remove any stray quotation marks within the term
		search_term_clean = search_term.replace('"', '').replace("'", '').strip()

	# Build the 'words_pattern' based on whether the term is quoted
	if is_quoted:
		# Only match the full phrase
		escaped_phrase = re.escape(search_term_clean)
		words_pattern = r'\b{}\b'.format(escaped_phrase)
	else:
		# Match both the full phrase and its individual words
		search_terms = [search_term_clean] + search_term_clean.split()
		escaped_terms = [re.escape(term) for term in search_terms]
		words_pattern = r'\b(' + '|'.join(escaped_terms) + r')\b'

    # Process the results
	for id1, id2, board, message, subject, files, date in results:
		if (outnum<1000):
			board = bytes(board).decode('utf-8')
			#out += (f'Board: {board} ')
			#out += (f'TID: <a href="/{board}/t/{id1}">{id1}</a> ')
			#out += (f'PID: <a href="/{board}/t/{id1}#{id2}">{id2}</a> ')



			# Process the subject
			subject_text = (
				bytes(subject).decode("utf-8") + ' ' if isinstance(subject, memoryview) else ""
			)
			# Use the updated 'words_pattern' in the lambda function
			subject_highlighted = re.sub(
				pattern,
				lambda match: (
					match.group(0) if match.group(1) else
					re.sub(words_pattern, r'<mark>\g<0></mark>', match.group(2), flags=re.IGNORECASE)
				),
				subject_text,
				flags=re.IGNORECASE | re.MULTILINE
			)

			# Process the message
			if isinstance(message, memoryview):
				message_content = (

					grab_server.escapeMessage(
						bytes(message).decode("utf-8"),
						id1,
						board
					) 
				)
			else:
				message_content = ""
			message_highlighted = re.sub(
				pattern,
				lambda match: (
					match.group(0) if match.group(1) else
					re.sub(words_pattern, r'<mark>\g<0></mark>', match.group(2), flags=re.IGNORECASE)
				),
				message_content,
				flags=re.IGNORECASE | re.MULTILINE
			)
			
			filelist=""
			
			if isinstance(files, memoryview):
				files=bytes(files).decode("utf-8")

			files=json.loads(files)

			for file in files:
				try:
					localpath=f"./media/{file['thumb'][7:]}.webp"
					localpath2=f"./media/{file['thumb'][7:]}.png"

					

					if os.path.exists(localpath)==True:
						width,height=imagesize.get(localpath)
						filelist+=f'<img src="{localpath}" style="max-width:165px;float:left;margin-right:10px;margin-bottom:10px;"  width="{width}" height="{height*(165/width) if width>165 else height}" loading="lazy">'
					elif os.path.exists(localpath2)==True:
						width,height=imagesize.get(localpath2)
						filelist+=f'<img src="{localpath2}" style="max-width:165px;float:left;margin-right:10px;margin-bottom:10px;"  width="{width}" height="{height*(165/width) if width>165 else height}" loading="lazy">'

				except:
					continue
			
			if message_highlighted != "":
				if len(message_highlighted)>1000:
					message_highlighted=message_highlighted[:1000]
					message_highlighted+=f'...</pre><pre style="display:inline;"><a href="/{board}/t/{id1}"><b>[expand thread]</b></a>'
				out += (f'<div id="post" class="post"><i><b id="postTitle">{subject_highlighted}</b> found in <a href="/{board}/t/{id1}#{id2}">{id1}</a> ({bytes(date).decode("utf-8")})<br><pre>{filelist}{message_highlighted}</pre></i></div><br>')
			#out+=('<br>')
				outnum+=1

	if (outnum>=500):
		out+="Too many results"



	arrPrep={}

	if 'wide' in session:
		if session['wide']==True:
			wideFormat='max-width:780px;'
		elif session['wide']==False:
			wideFormat=''
	else:
		wideFormat='max-width:780px;'
	
	if search_term==" ":
		search_term="<i>-blank-</i>"
	
	arrPrep=f'''<table style="width:100%;margin-top:-5px;{wideFormat}font-size:10pt;"><tbody><tr><td style="text-align: left;width:26%;"><a href="/">↑ Home</a>'''+grab_server.generateSettings()+f'''       </td><td style="text-align:center;">Searched for <b>{search_term}</b> ({str(len(results))} results)</td><td style="text-align: right;width:35%;"><div class="boardList" style="max-width: 30%;">'''

	arrPrep+=f'<span>↷ /all/</span><div class="boardList-content">'

	for v in settings.temp_prefs:
		arrPrep+=f'<p><a href="/{v}">/{v}/</a></p>'    
	
	arrPrep+=f'</div></div> <form action="/search" method="get" style="display:inline;"><input style="width:61%;min-width:87.95px;font-size:10pt;max-width: 50%" type="text" placeholder="Search all boards" id="q" name="q"> <input style="font-size:10pt;" type="submit" value="Find"></form></td></tr></tbody></table><hr><div id="empty" style="padding-top:4px"></div>'


	return '<!DOCTYPE html><head><title>KC-Archive</title><meta name = "viewport" content = "width = device-width"></head>'+grab_server.generatePageFormat(session)+arrPrep+str(out)

# grab_search.py

import os,requests,math,json,copy,html,time,re,traceback
from datetime import datetime
from PIL import Image

import grab_server,settings

def replace_outside_tags(match):
    if match.group(1):  # If it's inside a tag
        return match.group(0)  # Return the original match
    else:  # If it's outside a tag
        text = match.group(2)
        
        # Split the search term into individual words
        search_terms = [search_term] + search_term.split()
        
        # Create a regex pattern to match the search term and its words
        words_pattern = r'\b(' + '|'.join(map(re.escape, search_terms)) + r')\b'
        
        # Replace matches with <mark> tags
        processed_text = re.sub(words_pattern, r'<mark>\1</mark>', text, flags=re.IGNORECASE)
        return processed_text

def searchTables(session,search_term,threadsList,tic):

	page=""
	res2=""
	
	imgList=""
	resultsDis=0

	search_term=search_term.lower()
	
	for a in threadsList:
		
		messageouter=(str(threadsList[a])).lower()

		hit=False

		try:
			messageouter_message=(str(threadsList[a]['message'])).lower()
			messageouter_subject=(str(threadsList[a]['subject'])).lower()
			messageouter_uri=str(threadsList[a]['boardUri'])
		except:
			continue	
		
		sizeImages=0
		countImages=1
		currentIteration=0
		
		if search_term in messageouter_message or search_term in messageouter_subject:
			hit=True
			messageouter=grab_server.escapeMessage(threadsList[a]['message'],threadsList[a]['threadId'],threadsList[a]['boardUri'])
			messageouter = re.sub(rf'(<[^>]*>)|({search_term})', replace_outside_tags, messageouter, flags=re.IGNORECASE | re.MULTILINE)
			imgList,countImg,currentIteration=grab_server.gatherThumbs(threadsList[a],imgList,sizeImages,countImages,0,False,True)
			res2+=f"<div id='post' class='post'><i><a href='/{messageouter_uri}/t/{threadsList[a]['threadId']}'>found in {threadsList[a]['threadId']}</a> ({threadsList[a]['creation']})<br><pre>{imgList}{messageouter}</pre></i></div><br>"
			
			imgList=""
		for b in (threadsList[a]['posts']):
			messageinner=(str(b['message'])).lower()
			
			if search_term in messageinner:
				hit=True
				messageinner=grab_server.escapeMessage(b['message'],threadsList[a]['threadId'],threadsList[a]['boardUri'])
				messageinner = re.sub(rf'(<[^>]*>)|({search_term})', replace_outside_tags, messageinner, flags=re.IGNORECASE | re.MULTILINE)
				imgList,countImg,currentIteration=grab_server.gatherThumbs(b,imgList,sizeImages,countImages,0,False,True)
				res2+=f"<div id='post' class='post'>found in <a href='/{messageouter_uri}/t/{threadsList[a]['threadId']}#{str(b['postId'])}'>{b['postId']}</a> ({b['creation']})<br><pre>{imgList}{messageinner}</pre></div><br>"


				
				resultsDis+=1
			imgList=""
					
			if resultsDis>1000:		
				break
				
		if hit==True:
			res2+=""
					
		if resultsDis>1000:
			break
		
	

	arrPrep={}

	if 'wide' in session:
		if session['wide']==True:
			wideFormat='max-width:780px;'
		elif session['wide']==False:
			wideFormat=''
	else:
		wideFormat='max-width:780px;'
	
	if search_term==" ":
		search_term="<i>-blank-</i>"
	
	arrPrep=f'''<table style="width:100%;margin-top:-5px;{wideFormat}font-size:10pt;"><tbody><tr><td style="text-align: left;width:26%;"><a href="/">↑ Home</a>'''+grab_server.generateSettings()+f'''       </td><td style="text-align:center;">Searched for <b>{search_term}</b></td><td style="text-align: right;width:35%;"><div class="boardList" style="max-width: 30%;">'''

	arrPrep+=f'<span>↷ /all/</span><div class="boardList-content">'

	for v in settings.temp_prefs:
		arrPrep+=f'<p><a href="/{v}">/{v}/</a></p>'    
	
	arrPrep+=f'</div></div> <form action="/search" method="get" style="display:inline;"><input style="width:61%;min-width:87.95px;font-size:10pt;max-width: 50%" type="text" placeholder="Search all boards" id="q" name="q"> <input style="font-size:10pt;" type="submit" value="Find"></form></td></tr></tbody></table><hr><div id="empty" style="padding-top:4px"></div>'

	toc = time.perf_counter()
	res2+=f"Too many results ({toc - tic:0.4f} secs)"
	
	page = '<!DOCTYPE html><head><title>KC-Archive</title><meta name = "viewport" content = "width = device-width"></head>'+grab_server.generatePageFormat(session)+arrPrep+page+res2

	
	return page



(example row: "New gf just dropped."	"{""signedRole"":null,""id"":null,""name"":""Bernd"",""email"":null,""boardUri"":""int"",""threadId"":24545456,""flag"":""/.static/flags/br.png"",""flagCode"":""-br"",""flagName"":""Brasil"",""subject"":null,""markdown"":""New gf just dropped."",""message"":""New gf just dropped."",""creation"":""2024-10-04T03:58:26.118Z"",""locked"":false,""archived"":false,""pinned"":false,""cyclic"":false,""autoSage"":false,""files"":[{""originalName"":""1500chan.org-ba39c0561bfea4a8346134815a5518d6(mp4).mp4"",""path"":""/.media/d9cd4450920384f94c2b1e883b82f2d82bee026e073e374873920d32e897aaf0.mp4"",""thumb"":""/.media/t_d9cd4450920384f94c2b1e883b82f2d82bee026e073e374873920d32e897aaf0"",""mime"":""video/mp4"",""size"":5375193,""width"":720,""height"":1280},{""originalName"":""1500chan.org-96e7e437e0a2877db19df58d2adc711f(mp4).mp4"",""path"":""/.media/8957481cd198e2b536f2e22bab449f57994e4ff3e0495f47c3fdd24ce572a345.mp4"",""thumb"":""/.media/t_8957481cd198e2b536f2e22bab449f57994e4ff3e0495f47c3fdd24ce572a345"",""mime"":""video/mp4"",""size"":8634197,""width"":1080,""height"":1920},{""originalName"":""1500chan.org-14636a5b62e1e1646cfb1f27801e3d35(mp4).mp4"",""path"":""/.media/b7f8886956d3a2dab6b7afe7e5434ab3d657aab11581d392107ce08b012d8892.mp4"",""thumb"":""/.media/t_b7f8886956d3a2dab6b7afe7e5434ab3d657aab11581d392107ce08b012d8892"",""mime"":""video/mp4"",""size"":17867565,""width"":1080,""height"":1920},{""originalName"":""1500chan.org-638e704a3aaef778b082d06d3923870f(mp4).mp4"",""path"":""/.media/4d89e3148102e3ecd2bcb031d3b0ecff3278229f70f3bf96d604ce8b7862b3c8.mp4"",""thumb"":""/.media/t_4d89e3148102e3ecd2bcb031d3b0ecff3278229f70f3bf96d604ce8b7862b3c8"",""mime"":""video/mp4"",""size"":8995629,""width"":1080,""height"":1920}],""posts"":[{""name"":""Bernd"",""signedRole"":null,""email"":""sage"",""flag"":""/.static/flags/us.png"",""flagName"":""United States of America"",""id"":null,""subject"":null,""flagCode"":""-us"",""markdown"":""<a class=\""quoteLink\"" href=\""/int/res/24545456.html#24545456\"">&gt;&gt;24545456</a>\nToo old not a loli and a whore"",""message"":"">>24545456\r\nToo old not a loli and a whore"",""postId"":24545458,""creation"":""2024-10-04T04:00:25.815Z"",""files"":[]}],""uniquePosters"":0,""maxMessageLength"":16384,""usesCustomCss"":false,""reportCategories"":[""Spam"",""Illegal content"",""Other""],""wsPort"":8443,""usesCustomJs"":false,""boardName"":""International"",""boardDescription"":""."",""boardMarkdown"":""<a href=\""https://webchat.euirc.net/?channels=#kohlchan-en\"">#kohlchan-en</a> | <a href=\""https://github.com/KCstation/KCstation/wiki\"">KCstation</a> | <a href=\""https://reversi.dietchan.org/\"">Reversi</a> | <a href=\""/int/map\"">Poster Map</a> | <a href=\""https://1941.booru.org/index.php\"">Kohlbooru</a> | <a href=\""https://kohlchan.ws/\"">kohlchan.ws</a> | <a href=\""https://ernstchan.top/\"">Ernstchan<strong>.TOP</strong></a>"",""maxFileCount"":4,""maxFileSize"":""100.00 MB"",""forceAnonymity"":true}"	"2024-10-04T03:58:30.770Z"	24545456	2	2	1)