Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux