word: form synonyms from alt column

[sheet.git] / tools / word.pg.sql

diff --git a/tools/word.pg.sql b/tools/word.pg.sql
index da22b55311246348d8832e07542511b9aa3815d0..c1aa3c811322177975a86648ede30fee404b3ccf 100644 (file)
--- a/tools/word.pg.sql
+++ b/tools/word.pg.sql
@@ -1,5 +1,6 @@
 CREATE TABLE word (
        form       text        NOT NULL,
 CREATE TABLE word (
        form       text        NOT NULL,

+       alt        text[],

        lang       text        NOT NULL DEFAULT 'eng',
        cat        integer              REFERENCES word (id),
        ref        integer              REFERENCES word (id),
        lang       text        NOT NULL DEFAULT 'eng',
        cat        integer              REFERENCES word (id),
        ref        integer              REFERENCES word (id),


@@ -12,6 +13,7 @@ CREATE TABLE word (
 );
 
 COMMENT ON COLUMN word.form       IS 'preferred textual representation';
 );
 
 COMMENT ON COLUMN word.form       IS 'preferred textual representation';

+COMMENT ON COLUMN word.alt        IS 'alternate forms with equivalent meaning';

 COMMENT ON COLUMN word.lang       IS 'ISO 639-3 language code';
 COMMENT ON COLUMN word.cat        IS 'hierarchical classification';
 COMMENT ON COLUMN word.ref        IS 'reference to equivalent eng translation';
 COMMENT ON COLUMN word.lang       IS 'ISO 639-3 language code';
 COMMENT ON COLUMN word.cat        IS 'hierarchical classification';
 COMMENT ON COLUMN word.ref        IS 'reference to equivalent eng translation';


@@ -20,8 +22,13 @@ 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';
 
 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 FUNCTION exportform(word) RETURNS text AS $$
+       SELECT array_to_string($1.form || $1.alt, '/');
+$$ LANGUAGE SQL IMMUTABLE;
+

 CREATE OR REPLACE VIEW _cat_words AS
 CREATE OR REPLACE VIEW _cat_words AS

-       SELECT * FROM word RIGHT JOIN (
-               SELECT cat id, array_agg(form ORDER BY form) forms
+       SELECT exportform(word.*) form, sub.*, word.lang, word.ref
+       FROM word RIGHT JOIN (
+               SELECT cat id, array_agg(exportform(word.*) ORDER BY form) forms

                        FROM word WHERE ref IS NULL GROUP BY cat
        ) sub USING (id);
                        FROM word WHERE ref IS NULL GROUP BY cat
        ) sub USING (id);