PostgreSQL: CASE: SELECT FROM two different tables

case, create-function, postgresql, select, sql

Solution

Algorithm

- Select `1` into `a` (authorized) if we find a `user_id_1`-`session_id` match.

- Select `0, NULL, NULL` into `u` (unauthorized) if we didn't find a match in step 1.

- Select `user_id, body, sent` into `s` (select) if we did find a match in step 1.

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

Problem

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; ``` Ideally, could I wrap the above in a function with `TRUE` being an argument? 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.

Original source