Re: Using a boolean column with IF / THEN

[Alexander Farber]

On Sat, Dec 5, 2020 at 9:00 PM David G. Johnston 
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!

Re: Using a boolean column with IF / THEN

[David G. Johnston]

On Saturday, December 5, 2020, Alexander Farber 
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 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 -
>
>
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.

Using a boolean column with IF / THEN

[Alexander Farber]

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_sidtext,
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;