postgresql – plgpsql – 是否可以声明setof记录或表变量？

CREATE FUNCTION permission_cache_update(affected_user_list int[])
  RETURNS TABLE(user_id INT4,permission_id INT4)
AS
  $BODY$
  DECLARE
    current_relations SETOF RECORD;
  BEGIN

  WITH
  affected_user AS (
     SELECT unnest(affected_user_list) AS u_id
  ),affected_relations AS (
    SELECT user_role.user_id,role_permission.permission_id
      FROM   user_role
      JOIN   role_permission ON user_role.role_id = role_permission.role_id
      JOIN   affected_user ON affected_user.u_id = user_role.user_id
    UNION
    SELECT user_permission.user_id,user_permission.permission_id
      FROM   user_permission
      JOIN   affected_user ON affected_user.u_id = user_permission.user_id
  )
  SELECT affected_relations.user_id,affected_relations.permission_id FROM affected_relations
  INTO current_relations;

  DELETE FROM permission_cache WHERE ARRAY[user_id] <@ affected_user_list;
  INSERT INTO permission_cache (user_id,permission_id) SELECT user_id,permission_id FROM current_relations;

  END
  $BODY$
LANGUAGE plpgsql;

我想将current_relations中的当前用户权限关系存储为(INT4,INT4).是否可以使用没有循环和临时表的变量来执行此操作？

我稍后会使用这样的东西,所以我真的需要它作为变量,而不是子查询

DELETE FROM permission_cache WHERE ARRAY[user_id] <@ affected_user_list AND NOT IN (SELECT user_id,permission_id FROM current_relations);

INSERT INTO permission_cache (user_id,permission_id FROM current_relations WHERE NOT EXIST (SELECT user_id,permission_id FROM permission_cache);

我认为有可能用表 – > 2d阵列转换,但这很复杂,所以如果有可能记录,那会更好……

解：

使用1d数组而不是使用记录或2d数组创建循环更容易：

CREATE FUNCTION permission_cache_update(
  IN affected_user_list INT4 []
)
  RETURNS VOID
AS
  $BODY$
  DECLARE
    user_index                    INT4;
    current_user_id INT4;
    current_permission_relations  INT4 [];
    deleted_permission_relations  INT4 [];
    inserted_permission_relations INT4 [];
  BEGIN
    FOR user_index IN 1 .. array_upper(affected_user_list,1) LOOP
      current_user_id := affected_user_list[user_index];

      WITH
          user_permission_summary AS
        (
          SELECT
            role_permission.permission_id
          FROM user_role,role_permission
          WHERE role_permission.role_id = user_role.role_id AND user_role.user_id = current_user_id
          UNION
          SELECT
            user_permission.permission_id
          FROM user_permission
          WHERE user_permission.user_id = current_user_id
        )
      SELECT
        array_agg(permission_id)
      FROM user_permission_summary
      INTO current_permission_relations;

    SELECT
      array_agg(permission_cache.permission_id)
    FROM permission_cache
    WHERE permission_cache.user_id = current_user_id AND ( current_permission_relations IS NULL OR
          NOT (ARRAY [permission_cache.permission_id] <@ current_permission_relations) )
    INTO deleted_permission_relations;

    SELECT
      array_agg(inserted_permission_id)
    FROM unnest(current_permission_relations) AS inserted_permission_id
    WHERE NOT EXISTS(SELECT
                       1
                     FROM permission_cache
                     WHERE permission_cache.user_id = current_user_id AND
                           permission_cache.permission_id = inserted_permission_id)
    INTO inserted_permission_relations;

    DELETE FROM permission_cache
    WHERE permission_cache.user_id = current_user_id AND
          permission_cache.permission_id = ANY (deleted_permission_relations);

    INSERT INTO permission_cache (user_id,permission_id)
      SELECT
        current_user_id,inserted_permission_id
      FROM unnest(inserted_permission_relations) AS inserted_permission_id;

    END LOOP;
  END;
  $BODY$
LANGUAGE plpgsql VOLATILE;

关：
Pplpgsql是一种非常差的语言,文档甚至很差,我不喜欢它…
对不起代码格式化,我的IDE中的autoformat不太好……：S