Hi Laurenz, thanks for your reply, but I think it is wrong -
On Wed, Feb 16, 2022 at 1:24 PM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote:
One reason could be index corruption. If one query uses an index and the other doesn't,
that could lead to different results.
The other option is of course a trivial error, like you are using a different search
string or connect to a different database.
if you go to an online PostgreSQL editor like
and just enter my simple test code below,
you will see that wrongly "__CORRECT__" is printed:
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');
