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

[Laurenz Albe]

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...)

[Alexander Farber]

s/grumbling/wondering/

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

[Alexander Farber]

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...)

[Ralf Schuchardt]

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...)

[Alexander Farber]

Ah, I have to do

RETURN '___WRONG___';

and not

out_text := '___WRONG___'
RETURN;

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 
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...)

[Laurenz Albe]

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