From 1aa2a631dbc085fcdf72c9e6cef0a0de9a67f0a4 Mon Sep 17 00:00:00 2001 From: Mischa POSLAWSKY Date: Mon, 25 May 2020 05:24:51 +0200 Subject: [PATCH] word: select mkwordlist forms from _cat_words view --- tools/mkwordlist | 6 +----- tools/word.pg.sql | 6 ++++++ 2 files changed, 7 insertions(+), 5 deletions(-) diff --git a/tools/mkwordlist b/tools/mkwordlist index bec1362..d4dfaa2 100755 --- a/tools/mkwordlist +++ b/tools/mkwordlist @@ -1,8 +1,4 @@ #!/bin/sh psql sheet -XAt -c " - SELECT json_object_agg(coalesce(form, ''), forms) - FROM word RIGHT JOIN ( - SELECT cat id, array_agg(form ORDER BY form) forms - FROM word WHERE ref IS NULL GROUP BY cat - ) sub USING (id) + SELECT json_object_agg(coalesce(form, ''), forms) FROM _cat_words " | sed 's/:/=>/g' diff --git a/tools/word.pg.sql b/tools/word.pg.sql index 6cab79d..da22b55 100644 --- a/tools/word.pg.sql +++ b/tools/word.pg.sql @@ -19,3 +19,9 @@ COMMENT ON COLUMN word.prio IS 'difficulty level or importance; lower valu COMMENT ON COLUMN word.source IS 'URI of downloaded image'; COMMENT ON COLUMN word.thumb IS 'ImageMagick convert options to create thumbnail from source image'; COMMENT ON COLUMN word.wptitle IS 'reference Wikipedia article'; + +CREATE OR REPLACE VIEW _cat_words AS + SELECT * FROM word RIGHT JOIN ( + SELECT cat id, array_agg(form ORDER BY form) forms + FROM word WHERE ref IS NULL GROUP BY cat + ) sub USING (id); -- 2.30.0