Добрый день всем.

В оракле 12 есть 4 вида документов - для примера возьмем один
create table DK of xmltype store as binary xml;
--create one virtual column with value extracted from xml
alter table DK add (DKID NUMBER AS (EXTRACTVALUE(OBJECT_VALUE ,'/DKID/@ID'))) ;
create unique index DK_UKC on DK(DKID);

Есть pkg1.xmlrefresh(rec.DKID) пишущая xml в object_value
(SQL хитрый запрос по обычным таблицам )

ctx_ddl.create_section_group('DkXMLGR','XML_SECTION_GROUP');
ctx_ddl.add_attr_section('DKXMLGR','origin','DKID@ORIGIN');
ctx_ddl.add_field_section('DKXMLGR','text','BODY');
... и еще 20+

Есть XML индекс
create index DKXML_CTX on DKXML(OBJECT_VALUE) indextype is CTXSYS.CONTEXT
PARAMETERS('LEXER OISXML_LX WORDLIST OISXML_WL STOPLIST OISXML_SL filter CTXSYS.NULL_FILTER section group DKXMLGR');

написан пакет с функциями поиска которые и дергаются в app
типа : select * from table (PKG1.f_find(0,30,'/',sys.ku$_vcnt('TEST/text'),'user1',sys.ku$_vcnt('firewal1','firewal2'));
выдает таблицу ид док , статус , дата найденных доков (по хитрым условиям)

Я Не автор но вносил небольшие изменения (когда нужно было добавлять поиск по новым атрибутам) Не меняя логики
Работает в принципе хорошо и быстро

Стоит вопрос переноса этого дела на Postgres Azure
и пока у меня большие проблемы с общим подходом
Если кто-то делал что-то похожее или есть идеи пишите.
Я немного полазил в Postgres там тоже есть полнотекстовый поиск но практически уверен что той функциональснти не хватит

1?) Generated columns появились в postgres 12 (его пока у меня нет )
можно ли его вытянуть с xml поля и по нему сделать уникальный индекс

2?) Ссылки на толковые статьи по полнотекстовому поиску в Postgres велком (гуглом я польовался )

Ну или общие идеи тоже приветствюутся

@Melkij
Конечная задача написать набор функций такого вида :
Select * from table (PKG1.f_find(0,30,'/',sys.ku$_vcnt('TEST/text'),'user1',sys.ku$_vcnt('firewal1','firewal2'));
выдает таблицу ид док , статус , дата найденных доков (по хитрым условиям)
F_FIND( N_SKIP NUMBER, N_TOP NUMBER, P_DELIM VARCHAR2, ...) RETURN Tp_COLumns_IDS PIPELINED ;
.. PKG1.F_FINDCREAT_ON(sysdate-2,30,'/',SYS.KU$_VCNT('test/text','34512/clientid'),'user2',0,SYS.KU$_VCNT('firewal3','firewal4'));
...

чтобы их можно было вызывать для поиска документов с бакенда (там есть функции с параметрами дата от, дата по и т.д )

понятно пакетов нет и передавать массив надо не через sys.ku$_vcnt( а по другому (по моему там есть array))
Насчет xml - да возможно он и не нужен будет - в оракле по нему строился полнотекстовый поиск.
Насчет логики :
Атрибуты документа {attr1 , attr2 , attr3 , ... attrn }
Входят в разные фильтры поиска { Filter1 , ... FilterrN } в различных заранее известных сочетаниях (тот самый хитрый SQL)
Filter1 : { User_Sent , User_CC , Use_BCC , User_Boss ... }
Filter2 : { User_Sent , User_CC }
Фактически XML полученный в оракле это есть расширенный исходный документ
с б О льшим кол-вом тэгов чем исходных атрибутов
по которому сторится оракл. полнотектовый индекс по Различным тэгам.

Есть возможность выборки по этим заранее определенным фильтрам
, по разным категориям атрибутов документов (дата действия, статус , тип ... )
с учетом файрволов ( правила секурити юзер имеет право смотреть доки только по тем файрволам на к-е у него есть права
(для простоты считаем что 1 документ принадлежит 1 файрволу)

Пока я хочу понять можно ли попробовать прикрутить Postgres text search к-й вроде есть

Попробую переформулировать вопрос
Как переписать функции F_FIND*( в Postgres

т.е мои идеи пока своядтся к доп. таблицам
Sec_user : {dkid , user_id}
Sec_words {dkid , type_word , user_id} ...
Sec_date {dkid , dat_begin , dat_end }
и генерации динамического SQL по ним для разных случаев
почитал - вроде есть возможнсоть выдавать в функции динам. скл
RETURN QUERY EXECUTE ''
https://carto.com/help/working-with-data/sql-stored-procedures/

Идея вообщем понятна
Есть фильтр на юзеров - клеить таблицу Sec_user и т.д ..
пока думаю - в оригинале был 1 xml для 1 документа -
тут у меня получается миниму 3-4 доп. таблицы с атрибутами
Опять же перформанс с динамическим SQL это еще та загадка и проблема.
В одном случае будет быстро - в другом тормоза

В SQL server я бы делал через последтовательность временных таблиц ( #Tmp1, #Tmp2 , #Tmp3 )
(между ними можно и лог всунутьт (чтобы видет перфоманс) и доп. индексы на #Tmp создать )
чтобы не создавать один мегасложный запрос - а можно ли в postgres не ясно - вряд ли.

Вообщем идеи КАК можно приветсвуются

Maxim Boguk,
СПАСИБО за ответ
почитаю мат. часть
а где и как можно посмотерть
поддерижваются ли
jsonb и hstore
в Azure Postgres 10(11) ?

Опять актуально
допустим в body емейла (обычное письмо - м.б несколько страниц текста )
есть текст : word1 word2 word3 word4 ...

оракл. текст серч
select * from table (f_find(0,30,'/',sys.ku$_vcnt('"word1 word2 word3 word4" /text')));
находит сей документ (для простоты разделитель пока только через пробел )

select * from table (f_find(0,30,'/',sys.ku$_vcnt('"word1 word2 word3" /text'))); - тот же документ

select * from table (f_find(0,30,'/',sys.ku$_vcnt('"word1 word2" /text'))); -- находит 2 документа
select * from table (f_find(0,30,'/',sys.ku$_vcnt('"word1" /text'))); -- очень много

если я сделаю промежуточную таблицу
dkid word1
dkid word2
и буду генерить динам. скл - я найду по одному слову только
(если поставить условие AND на вхождения я найду его но даже в случае
word1 , dop1 , word2 , 'dddd' ,3 ,word3 )
т.е с допущениями - уже не 100% совпадание логики.

делать все предложения (dkid : word1 word2 word3 ...) - идея явно не рабочая.

Получается велосипед с нормал. таблицами и динам. скл не прокатит тут.

Переформулирую вопрос
есть ли гдE толковые статьи (блоги)
по практическому использованию полнотекстового поиска в postgres 11 (Azure)
https://www.postgresql.org/docs/11/textsearch-intro.html
вот эта дока увы не устраивает

мне нужен поиск по разным группам атрибутов в качестве параметра
в идеале чтобы я сделал xml - с тегами а постгрес проиндексировал


зы скорей всего это уйдет на бак-енд - типа эластик серч (но пока еще ищу варианты)

поискал умных статей
https://dev.to/akshaynathan/keep-it-simple-stupid-building-full-text-search-across-all-your-tools-with-postgres-27ho
https://blog.crunchydata.com/blog/postgres-full-text-search-a-search-engine-in-a-database
https://rocky.dev/full-text-search

взял пример с последнего (без генерации данных )
SELECT * FROM search_idx WHERE to_tsvector('english'::regconfig, val_str) @@ to_tsquery('w1 w2') ;
SELECT to_tsquery('w1 w2') ; -- SQL Error [42601]: ERROR: syntax error in tsquery: "w1 w2"

пробел сразу все и убивает
т.е поиск фраз с пробелами не возмжне в принципе ?