DROP TABLE words_nouns;
DROP TABLE words_verbs;
CREATE TABLE words_nouns (
word text PRIMARY KEY, -- CHECK is added below
hashed text NOT NULL,
expl text
);
CREATE TABLE words_verbs (
word text PRIMARY KEY, -- CHECK is added below
hashed text NOT NULL
);
ALTER TABLE words_nouns
ADD CONSTRAINT words_nouns_word_check
CHECK (
word ~ '^[А-Я]{2,}$' AND
word !~ '[ЖШ]Ы' AND
word !~ '[ЧЩ]Я'
);
ALTER TABLE words_verbs
ADD CONSTRAINT words_verbs_word_check
CHECK (
word ~ '^[А-Я]{2,}$' AND
word !~ '[ЖШ]Ы' AND
word !~ '[ЧЩ]Я' AND
word !~ 'Ц[ЮЯ]' AND
(word ~ '[ТЧ]ЬСЯ$' OR
word ~ '[ТЧ]Ь$' OR
word ~ 'ТИ$')
);
CREATE OR REPLACE FUNCTION words_guess_puzzle(
in_mid bigint,
in_guess text,
OUT out_text text
) RETURNS text AS
$func$
BEGIN
in_guess := UPPER(in_guess);
-- check if the in_guess is a valid word - WHY DOES THIS NEVER TRIGGER?
IF NOT EXISTS(SELECT 1 FROM words_nouns WHERE word = in_guess UNION
SELECT 1 FROM words_verbs WHERE word = in_guess) THEN
out_text := '___WRONG___'
RETURN;
END IF;
out_text := '___CORRECT___'
RETURN;
END
$func$ LANGUAGE plpgsql;
SELECT words_guess_puzzle(123, 'ABCDE');