PostgreSQL如何改造视图实现传入user_id自动查询用户权限下的相机数据

结论 #

完全可以实现，以下是两种可直接落地的改造方案，你可以根据业务场景选择：

方案1：改造普通视图（低侵入，兼容原有调用逻辑） #

改完后仅需在查询时加WHERE user_id = 传入的用户ID即可自动完成权限校验，无需额外处理相机ID合并逻辑。
改造后的视图创建语句如下：

CREATE VIEW archives_view AS
WITH CORE AS (
    SELECT
      'ARCHIVES' AS TAB_NAME,
      ARC.TITLE,
      ARC.EXID,
      ARC.CREATED_AT,
      ARC.FROM_DATE,
      ARC.TO_DATE,
      NULL AS EMBED_CODE,
      ARC.FILE_NAME,
      ARC.FRAMES,
      ARC.URL,
      ARC.PUBLIC,
      ARC.STATUS,
      ARC.TYPE,
      NULL AS EXTRA,
      ARC.REQUESTED_BY,
      ARC.CAMERA_ID
    FROM PUBLIC.ARCHIVES AS ARC
    UNION ALL
    SELECT
      'TIMELAPSES',
      TL.TITLE,
      TL.EXID,
      TL.INSERTED_AT,
      TL.FROM_DATETIME,
      TL.TO_DATETIME,
      NULL AS EMBED_CODE,
      NULL AS FILE_NAME,
      NULL AS FRAMES,
      NULL AS URL,
      NULL AS PUBLIC,
      TL.STATUS,
      NULL AS TYPE,
      TL.EXTRA,
      TL.USER_ID,
      TL.CAMERA_ID
    FROM PUBLIC.TIMELAPSES AS TL
    UNION ALL
    SELECT
      'COMPARES',
      COMP.EXID,
      COMP.NAME,
      COMP.INSERTED_AT,
      COMP.BEFORE_DATE,
      COMP.AFTER_DATE,
      COMP.EMBED_CODE,
      NULL AS FILE_NAME,
      NULL AS FRAMES,
      NULL AS URL,
      COMP.PUBLIC,
      COMP.STATUS,
      NULL AS TYPE,
      NULL AS EXTRA,
      COMP.REQUESTED_BY,
      COMP.CAMERA_ID
    FROM PUBLIC.COMPARES AS COMP
),
MORE_INFO AS (
    SELECT
      C.*,
      TRIM(CONCAT(U.FIRSTNAME, ' ', U.LASTNAME)) AS REQUESTER_NAME,
      U.EMAIL AS REQUESTER_EMAIL,
      CAM.EXID AS CAMERA_EXID,
      CAM.NAME,
      CAM.NAME AS CAMERA_NAME,
      CAM.TIMEZONE AS CAMERA_TIMEZONE,
      CAM.OWNER_ID AS CAMERA_OWNER_ID
    FROM CORE C
    JOIN USERS U ON C.REQUESTED_BY = U.ID
    JOIN CAMERAS CAM ON C.CAMERA_ID = CAM.ID
),
-- 新增权限映射：枚举所有相机对应的可访问用户
CAMERA_ACCESS AS (
    -- 相机所有者权限
    SELECT ID AS CAMERA_ID, OWNER_ID AS ACCESS_USER_ID FROM CAMERAS
    UNION
    -- 相机共享权限
    SELECT CAMERA_ID, USER_ID AS ACCESS_USER_ID FROM CAMERA_SHARES
)
SELECT
  MI.*,
  CA.ACCESS_USER_ID AS USER_ID
FROM MORE_INFO MI
JOIN CAMERA_ACCESS CA ON MI.CAMERA_ID = CA.CAMERA_ID
ORDER BY CREATED_AT DESC;

使用方式 #

SELECT * FROM archives_view WHERE user_id = 待查询的用户ID;

方案2：表函数实现（高性能，适合大数据量场景） #

如果你的业务数据量较大，普通视图全量关联性能不足，可以用PostgreSQL表函数实现，直接传入用户ID作为参数，执行效率更高：

CREATE OR REPLACE FUNCTION get_user_archives(p_user_id INT)
RETURNS TABLE (
    TAB_NAME VARCHAR,
    TITLE VARCHAR,
    EXID VARCHAR,
    CREATED_AT TIMESTAMP,
    FROM_DATE TIMESTAMP,
    TO_DATE TIMESTAMP,
    EMBED_CODE VARCHAR,
    FILE_NAME VARCHAR,
    FRAMES INT,
    URL VARCHAR,
    PUBLIC BOOLEAN,
    STATUS VARCHAR,
    TYPE VARCHAR,
    EXTRA JSONB,
    REQUESTED_BY INT,
    CAMERA_ID INT,
    REQUESTER_NAME VARCHAR,
    REQUESTER_EMAIL VARCHAR,
    CAMERA_EXID VARCHAR,
    NAME VARCHAR,
    CAMERA_NAME VARCHAR,
    CAMERA_TIMEZONE VARCHAR,
    CAMERA_OWNER_ID INT
) AS $$
BEGIN
    RETURN QUERY
    WITH ALLOWED_CAMERAS AS (
        -- 先过滤当前用户有权限的相机
        SELECT ID AS CAMERA_ID FROM CAMERAS WHERE OWNER_ID = p_user_id
        UNION
        SELECT CAMERA_ID FROM CAMERA_SHARES WHERE USER_ID = p_user_id
    )
    SELECT
      C.TAB_NAME,
      C.TITLE,
      C.EXID,
      C.CREATED_AT,
      C.FROM_DATE,
      C.TO_DATE,
      C.EMBED_CODE,
      C.FILE_NAME,
      C.FRAMES,
      C.URL,
      C.PUBLIC,
      C.STATUS,
      C.TYPE,
      C.EXTRA,
      C.REQUESTED_BY,
      C.CAMERA_ID,
      TRIM(CONCAT(U.FIRSTNAME, ' ', U.LASTNAME)) AS REQUESTER_NAME,
      U.EMAIL AS REQUESTER_EMAIL,
      CAM.EXID AS CAMERA_EXID,
      CAM.NAME,
      CAM.NAME AS CAMERA_NAME,
      CAM.TIMEZONE AS CAMERA_TIMEZONE,
      CAM.OWNER_ID AS CAMERA_OWNER_ID
    FROM CORE C
    JOIN ALLOWED_CAMERAS AC ON C.CAMERA_ID = AC.CAMERA_ID
    JOIN USERS U ON C.REQUESTED_BY = U.ID
    JOIN CAMERAS CAM ON C.CAMERA_ID = CAM.ID
    ORDER BY C.CREATED_AT DESC;
END;
$$ LANGUAGE plpgsql STABLE;

使用方式 #

SELECT * FROM get_user_archives(待查询的用户ID);

优化提示 #

建议给以下字段加索引，可大幅提升查询性能：

• CAMERAS.OWNER_ID
• CAMERA_SHARES.USER_ID、CAMERA_SHARES.CAMERA_ID

内容的提问来源于stack exchange，提问作者Junaid Farooq