On Wed, 2022-02-16 at 11:51 +0100, Alexander Farber wrote:
> when I search for a non existent word in the two tables hosted in PostgreSQL 14.1 then I get zero records as
expected:
>
> words_en=> SELECT 1 FROM words_nouns WHERE word = 'ABCDE' UNION SELECT 1 FROM words_verbs WHERE word = 'ABCDE';
> ?column?
> ----------
> (0 rows)
>
> 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$
> [...]
> -- 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;
> [...]
>
> What could be the reason please?
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.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com