PostgreSQL: CASE: SELECT FROM two different tables

Question

1. Is it possible to do something like the following with SQL, not PL/pgSQL (note if it's only possible with PL/pgSQL, then how)?

   IF password = 'swordfish' THEN
       SELECT a, b, c FROM users;
   ELSE
       SELECT -1; -- unauthorized error code
   END IF;
   

2. Ideally, could I wrap the above in a function with TRUE being an argument?

3. Rather, is it possible to set the command status string to -1?

I'm asking this because I want the query to return an error code, like -1, if someone tries to get a list of all the users with the wrong password. This is for a web app with user accounts that each have a password. So, this is not something I want to manage with database roles/permissions.

Answer 1

Algorithm

1. Select 1 into a (authorized) if we find a user_id_1-session_id match.
2. Select 0, NULL, NULL into u (unauthorized) if we didn't find a match in step 1.
3. Select user_id, body, sent into s (select) if we did find a match in step 1.
4. Union u and s.

Code

-- List messages between two users with `user_id_1`, `session_id`, `user_id_2`
CREATE FUNCTION messages(bigint, uuid, bigint) RETURNS TABLE(i bigint, b text, s double precision) AS
$$
    WITH a AS (
        SELECT 1
        FROM sessions
        WHERE user_id = $1
        AND id = $2
    ), u AS (
        SELECT 0, NULL::text, NULL::double precision
        WHERE NOT EXISTS (SELECT 1 FROM a)
    ), s AS (
        SELECT user_id, body, trunc(EXTRACT(EPOCH FROM sent))
        FROM messages
        WHERE EXISTS (SELECT 1 FROM a)
        AND chat_id = pair($1, $3)
        LIMIT 20
    )
    SELECT * FROM u UNION ALL SELECT * FROM s;
$$
LANGUAGE SQL STABLE;