when I search for a non existent word in the two tables hosted in PostgreSQL 14.1 then I get zero records as expected:
But when I try to use the same command in my stored function, then it goes through, as if the word would exist (and a new record is inserted into the words_puzzle table):
CREATE OR REPLACE FUNCTION words_guess_puzzle(
in_mid bigint,
in_social integer,
in_sid text,
in_auth text,
in_guess text,
OUT out_text text
) RETURNS text AS
$func$
DECLARE
_uid integer;
BEGIN
IF NOT words_valid_user(in_social, in_sid, in_auth) THEN
RAISE EXCEPTION 'Invalid user = % %', in_social, in_sid;
END IF;
_uid := (SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid);
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;
INSERT INTO words_puzzle (mid, uid, word, guessed)
VALUES (in_mid, _uid, in_guess, CURRENT_TIMESTAMP);
out_text := '___CORRECT___'
RETURN;
END
$func$ LANGUAGE plpgsql;