Re: if not exists (SELECT 1... UNION SELECT 1...)
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
Re: if not exists (SELECT 1... UNION SELECT 1...)
s/grumbling/wondering/
Re: if not exists (SELECT 1... UNION SELECT 1...)
Ah, thank you Ralf! That has explained it (because I was still grumbling...) On Wed, Feb 16, 2022 at 4:44 PM Ralf Schuchardt 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; > > >
Re: if not exists (SELECT 1... UNION SELECT 1...)
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
Re: if not exists (SELECT 1... UNION SELECT 1...)
Ah, I have to do RETURN '___WRONG___'; and not out_text := '___WRONG___' RETURN;
Re: if not exists (SELECT 1... UNION SELECT 1...)
Hi Laurenz, thanks for your reply, but I think it is wrong -
On Wed, Feb 16, 2022 at 1:24 PM Laurenz Albe
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');
Re: if not exists (SELECT 1... UNION SELECT 1...)
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
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_socialinteger, 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
