Обсуждение: Using a boolean column with IF / THEN
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;
On Saturday, December 5, 2020, Alexander Farber <alexander.farber@gmail.com> wrote:
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 mottomuted boolean NOT NULL DEFAULT false,Currently I check the value as follows, but I wonder if this is the best way with PL/pgSQL -
Maybe not “simpler” but for all those checks you could write a single query that pulls out all the data at once into a record variable and test against the columns pf that instead of executing multiple queries.
David J.
On Sat, Dec 5, 2020 at 9:00 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
Maybe not “simpler” but for all those checks you could write a single query that pulls out all the data at once into a record variable and test against the columns pf that instead of executing multiple queries.
Thank you!