Using a boolean column with IF / THEN

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Using a boolean column with IF / THEN
Date: 2020-12-05 19:28:14
Message-ID: CAADeyWhhFxEyp0ryKOnC3iQMGpR1ObfquvKdv1G-f2yXAi2zGQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2020-12-05 20:00:49 Re: Using a boolean column with IF / THEN
Previous Message Paul Förster 2020-12-05 15:04:14 Re: Accessing Postgres Server and database from other Machine