0

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?

1 Answer 1

2

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 ...)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.