Re: if not exists (SELECT 1... UNION SELECT 1...)

[Alexander Farber]

Hi Laurenz, thanks for your reply, but I think it is wrong -

On Wed, Feb 16, 2022 at 1:24 PM Laurenz Albe <laurenz.a...@cybertec.at>
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
https://extendsclass.com/postgresql-online.html

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');