66 lines
1.5 KiB
PL/PgSQL
66 lines
1.5 KiB
PL/PgSQL
create table item (
|
|
id bigint primary key,
|
|
data jsonb
|
|
);
|
|
|
|
create index idx_item_data
|
|
on item using gin (data jsonb_path_ops);
|
|
|
|
alter table item
|
|
add column description_tsv tsvector generated always as (
|
|
to_tsvector('french', data->>'description')
|
|
) stored;
|
|
|
|
create index idx_item_description_tsv
|
|
on item
|
|
using gin (description_tsv);
|
|
|
|
alter table item
|
|
add column search_tsv tsvector generated always as (
|
|
setweight(to_tsvector('french', coalesce(data->>'description', '')), 'A') ||
|
|
setweight(to_tsvector('french', coalesce(data->>'nom', '')), 'B') ||
|
|
setweight(to_tsvector('french', coalesce(data->>'categorie', '')), 'C')
|
|
) STORED;
|
|
|
|
|
|
CREATE INDEX idx_item_search_tsv
|
|
ON item
|
|
USING gin (search_tsv);
|
|
|
|
/*
|
|
SELECT
|
|
id,
|
|
ts_rank(search_tsv, plainto_tsquery('french', 'miel lavande')) AS rank
|
|
FROM item
|
|
WHERE search_tsv @@ plainto_tsquery('french', 'miel lavande')
|
|
ORDER BY rank DESC;
|
|
*/
|
|
|
|
truncate table item;
|
|
|
|
DO $$
|
|
DECLARE
|
|
f TEXT;
|
|
BEGIN
|
|
FOR f IN SELECT pg_catalog.pg_ls_dir('/tmp/json')
|
|
LOOP
|
|
IF right(f, 5) = '.json' THEN
|
|
BEGIN
|
|
RAISE NOTICE 'Import du fichier : %', f;
|
|
|
|
INSERT INTO item (id, data)
|
|
VALUES (
|
|
replace(f, '.json','')::bigint,
|
|
pg_read_file('/tmp/json/' || f)::jsonb
|
|
)
|
|
ON CONFLICT (id) DO UPDATE
|
|
SET data = EXCLUDED.data;
|
|
|
|
EXCEPTION WHEN OTHERS THEN
|
|
RAISE WARNING 'Erreur lors de l''import du fichier % : %', f, SQLERRM;
|
|
END;
|
|
END IF;
|
|
END LOOP;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|