How Can I Write a Postgres “if” Based on a SELECT

functionspostgresql

If I have a function in PostgreSQL, I can validate its arguments (eg. a username argument) like so:

IF LENGTH(username) < 4 THEN
    RAISE EXCEPTION 'Usernames must contain at least four characters';
END IF;

What I can't figure out is how to do the same thing with the results of a SELECT query, ie. something like:

IF LENGTH(SELECT * FROM USERS WHERE username=username) > 1 THEN
    RAISE EXCEPTION 'Username already exists';
END IF;

(And yes, I know PostgreSQL will give me an error without that line, but I want to have a friendlier error message.)

Is something like that possible in (Postgre)SQL, and if so can someone point me at the syntax (or an example of it) that I could look at?

Best Answer

Yes, that is possible, but the subquery needs an extra pair of parentheses:

length((SELECT col FROM users WHERE ...))

One pair of parentheses is for the subquery, one for the function call.

The more readable way to write this is

(SELECT length(col) FROM users WHERE ...)
Related Question