Search Postgresql Archives

Using a boolean column with IF / THEN

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

 



Good evening,

hopefully my question is not too stupid, but -

in a 13.1 database I have a words_users table with a boolean column:

        -- the user is not allowed to chat or change the motto
        muted         boolean NOT NULL DEFAULT false,

Currently I check the value as follows, but I wonder if this is the best way with PL/pgSQL -

        IF EXISTS (SELECT 1 FROM words_users
                            WHERE uid = _uid AND 
                            muted) THEN
                RAISE EXCEPTION 'User % is muted', _uid;
        END IF;

Or can this be done in a simpler way?

Thanks
Alex

P.S. Here my entire stored function:

CREATE OR REPLACE FUNCTION words_set_motto(
        in_social integer,
        in_sid    text,
        in_auth   text,
        in_motto  text
        ) RETURNS integer 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);

        IF LENGTH(in_motto) > 250 THEN
                RAISE EXCEPTION 'Invalid motto by user %', _uid;
        END IF;

        IF EXISTS (SELECT 1 FROM words_users
                            WHERE uid = _uid AND 
                            muted) THEN
                RAISE EXCEPTION 'User % is muted', _uid;
        END IF;

        IF (SELECT
                COUNT(NULLIF(nice, 0)) - 
                COUNT(NULLIF(nice, 1))
                FROM words_reviews 
                WHERE uid = _uid) < -20 THEN
                RAISE EXCEPTION 'User % can not change motto', _uid;
        END IF;

        UPDATE words_users
        SET motto = in_motto
        WHERE uid = _uid;

        RETURN _uid;
END
$func$ LANGUAGE plpgsql;


[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 Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux