Обсуждение: if not exists (SELECT 1... UNION SELECT 1...)
Hello,
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$
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;
What could be the reason please?
Best regards
Alex
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
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@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
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');
Ah, I have to do
RETURN '___WRONG___';
and not
out_text := '___WRONG___'
RETURN;
On 16 Feb 2022, at 14:36, Alexander Farber wrote:
Ah, I have to do
RETURN '___WRONG___';
Not necessarily.
and not
out_text := '___WRONG___'
RETURN;
You must rather end the assignment with a semicolon:
out_text := '___WRONG___';
RETURN;
Otherwise what really happens is:
out_text := '___WRONG___' RETURN;
where „return“ is a simple column name as in this select statement:
select '___WRONG___' return;
Yours,
Ralf
Ah, thank you Ralf! That has explained it (because I was still grumbling...)
On Wed, Feb 16, 2022 at 4:44 PM Ralf Schuchardt <rasc@gmx.de> wrote:
You must rather end the assignment with a semicolon:
out_text := '___WRONG___'; RETURN;Otherwise what really happens is:
out_text := '___WRONG___' RETURN;where „return“ is a simple column name as in this select statement:
select '___WRONG___' return;
s/grumbling/wondering/
On Wed, 2022-02-16 at 14:36 +0100, Alexander Farber wrote: > Ah, I have to do > > RETURN '___WRONG___'; > > and not > > out_text := '___WRONG___' > RETURN; Yes. As I said, a trivial error. Yours, Laurenz Albe