PostgreSQL：CASE：从两个不同的表中选择

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?

我之所以这样问是因为，如果有人试图使用错误的密码获取所有用户列表，我希望查询返回一个错误代码（如-1）。这是一个具有用户账户和密码的Web应用程序。因此，这不是我想要使用数据库角色/权限来管理的内容。

- ma11hew28

---

@trygvis 我更新了我的问题。好的。也许应该将第二个 SELECT 改为 -1，NULL，NULL？ - ma11hew28

@trygvis 一个简单的 SELECT 不会返回任何东西。提供的示例是有效的 pl/pgsql，只是什么也不做。 - pozs

@pozs 在问题中暗示这是一个非 PL/pgSQL 查询。 - Trygve Laugstøl

@trygvis 我想到了 SELECT ... WHERE password =，但我需要能够区分这两种情况：1.密码错误，2.表中实际上没有用户。而且，那个解决方案并不能做到这一点。 - ma11hew28

@trygvis 我另一个想法是使用带有 id = -1 的行来初始化 users（其中 id 是 serial），然后只需使用类似 ... WHERE CASE WHEN FALSE id = -1 的东西。但是，那似乎有点奇怪。 - ma11hew28

Answer 1

算法

1. 如果我们找到一个user_id_1-session_id匹配项，则将1选择为a（已授权）。
2. 如果在步骤1中没有找到匹配项，则将0，NULL，NULL选择为u（未授权）。
3. 如果在步骤1中找到了匹配项，则将user_id，body，sent选择为s（选择）。
4. 联合u和s。

代码

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

- ma11hew28

Answer 2

下面的PL/pgsql函数返回在user_id:key配对被用户自定义函数（UDF）user_auth确定为授权时，user_id和with_user_id之间发送的messages。否则，它将返回一行from = -1。另一个UDF pair 是一个唯一无序配对函数，给定两个用户ID，返回消息所属的chat_id。请保留HTML标签。

--- Arguments: user_id, key, with_user_id
CREATE FUNCTION messages(bigint, uuid, bigint)
RETURNS TABLE(from bigint, body text, sent double precision) AS $$
BEGIN
    IF user_auth($1, $2) THEN
        RETURN QUERY SELECT from, body, trunc(EXTRACT(EPOCH FROM sent))
                     FROM messages WHERE chat_id = pair($1, $3);
    ELSE
        i := -1;
        RETURN NEXT;
    END IF;
END;
$$ LANGUAGE plpgsql STABLE;

我不知道如何将此内容翻译为SQL函数，或者是否更好使用SQL函数。

- ma11hew28

---

变量i在该范围内未定义，而RETURN NEXT;没有返回任何内容（在ELSE分支中将得到一个空结果集）- 使用类似于RETURN NEXT ROW(-1,'auth error',EXTRACT(EPOCH FROM CURRENT_TIMESTAMP));的语句。 - pozs

Answer 3

这种方法可以运行，但不够优雅：

WITH 
  u AS (SELECT * FROM user WHERE mail = '..'),
  code AS (
    SELECT 
      CASE (SELECT count(*) FROM u)
      WHEN 0 THEN
        'not found'
      ELSE
        CASE (SELECT count(*) FROM u WHERE password = '..')
        WHEN 1 THEN
          'right password'
        ELSE
          'wrong password'
        END
      END)
SELECT
  code.*,
  u.*
FROM code NATURAL LEFT OUTER JOIN u

我认为你可能想要考虑创建一个返回结果集的函数。

- Trygve Laugstøl

---

有趣...谢谢。不过，这会返回每一行的代码。是否有一种使用PL/pgSQL的方法，使函数只返回错误代码或结果，而不是两者都返回？ - ma11hew28

我认为我已经通过PL/pgSQL找到了我想要的方法。 - ma11hew28

@MattDiPasquale 是的，那可能是最好的解决方案。 - Trygve Laugstøl

Answer 4

除了 (pl/pgsql only) CASE 控制结构 之外，还有一个 CASE 表达式。

编辑: sql 上下文中的 CASE 表达式:

SELECT CASE
           WHEN my_conditions_are_met THEN a
           ELSE NULL
       END AS a_or_null,
       b,
       c
FROM users;

编辑2：假设你的例子，这是如何在纯SQL中实现的：

WITH params AS (
    SELECT user_auth(:user_id, :key)     AS user_auth,
           pair(:user_id, :with_user_id) AS chat_id
), error_message AS (
    SELECT -1                                    AS "from",
           'auth error'                          AS "body",
           EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) AS "sent"
)
SELECT from, body, trunc(EXTRACT(EPOCH FROM sent))
FROM messages
JOIN params ON messages.chat_id = params.chat_id AND params.user_auth
UNION ALL
SELECT error_message.*
FROM error_message
JOIN params ON NOT params.user_auth

- pozs

---

我知道，但你怎么使用它来完成我的要求？ - ma11hew28

如果您需要确保所有值都为“NULL”，那么这将是一种痛苦，但如果只需要错误代码具有特定值，则它将工作得很好。 - Trygve Laugstøl

是的。但为什么要像正常结果一样返回错误代码呢？所有客户端都必须具备某种形式的错误处理和受影响行计数，这样更好。 - pozs

也许我应该让客户端先进行查询以检查是否my_conditions_are_met，如果是，则客户端再进行实际的查询。我只是想一次性完成所有操作并避免在数据库服务器上进行不必要的往返查询。 - ma11hew28

那要看你真正想要实现什么？为什么在你的原始示例中有一个 IF？ - pozs