plpgsql-cte使用数组参数化导致错误消息

Question

我将尝试向复杂的选择查询注入一个id数组

CREATE FUNCTION permission_cache_update(
  IN affected_user_list INT4[]
)
  RETURNS TABLE(user_id INT4, permission_id INT4)
AS
  $BODY$
  BEGIN
    RETURN QUERY
    WITH
    affected_user AS (
      SELECT unnest(affected_user_list) AS user_id
    ), 
    permission_summary AS
    (
      SELECT affected_user.user_id, role_permission.permission_id
      FROM user_role, role_permission, affected_user
      WHERE role_permission.role_id = user_role.role_id AND user_role.user_id = affected_user.user_id
      UNION
      SELECT affected_user.user_id, user_permission.permission_id
      FROM user_permission, affected_user
      WHERE user_permission.user_id = affected_user.user_id
    )
    SELECT user_id, permission_id
    FROM permission_summary
    GROUP BY user_id, permission_id;
  END;
  $BODY$
LANGUAGE plpgsql VOLATILE;

问题出在这个部分：

    affected_user AS (
      SELECT unnest(affected_user_list) AS user_id
    ), 

但我不知道如何修复它。错误消息毫无意义。

[42601] ERROR: syntax error at or near "$2"

我想使用user_id, permission_id对来更新包含相同值的权限缓存。因此，询问用户权限会更快，因为它们将被缓存在那里。我想使用数据库来存储和检查会话权限。我不知道这是否明智。。。

Answer 1

我从您之前的问题中得出，您使用的是PostgreSQL8.4，现在已经非常过时了
您需要为每个问题提供这些信息

在函数头中，您声明

RETURNS TABLE(user_id INT4, permission_id INT4)

OUT参数user_id和permission_id在PL/pgSQL中的函数体中随处可见。为了避免命名冲突，您需要表限定相同名称的列。您的函数也不能与现代Postgres一起正常工作，因为最终SELECT中的引用将返回NULL值：

SELECT user_id, permission_id

Postgres8.4有更多的问题和的错误

SELECT unnest(affected_user_list) AS user_id

命名冲突对于旧版本是不可接受的。较新版本已解决此问题。

sql函数

您可以将此SQL函数与大大简化的查询一起使用：

CREATE FUNCTION permission_cache_update(affected_user_list int[])
  RETURNS TABLE(user_id int, permission_id int) AS
$func$
WITH affected_user AS (SELECT unnest($1) AS user_id) 
SELECT a.user_id, r.permission_id
FROM   user_role       u
JOIN   role_permission r USING (role_id)
JOIN   affected_user   a USING (user_id)
UNION
SELECT a.user_id, p.permission_id
FROM   user_permission p
JOIN   affected_user   a USING (user_id)
$func$  LANGUAGE sql;

• 使用正确的联接语法。更易于阅读和维护。我还用USING进行了简化，这是一个品味和风格的问题。它更简单、更短，但需要明确的列名。因此，这在Postgres8.4的plpgsql版本中也不起作用，但在现代版本中起作用。

• 原始查询中带有GROUP BY的最终SELECT只是噪声。UNION（与UNION ALL相反）已经隐式地删除了重复项。

plpgsql函数

如果您避免了命名冲突，plpgsql版本也应该有效：

CREATE FUNCTION permission_cache_update(affected_user_list int[])
  RETURNS TABLE(user_id int, permission_id int) AS
$func$
BEGIN
RETURN QUERY
WITH affected_user AS (
   SELECT unnest(affected_user_list) AS u_id
   ) 
SELECT a.user_id, r.permission_id
FROM   user_role       u
JOIN   role_permission r USING (role_id)
JOIN   affected_user   a ON a.u_id = u.user_id
UNION
SELECT a.user_id, p.permission_id
FROM   user_permission p
JOIN   affected_user   a ON a.u_id = p.user_id;
END    
$func$  LANGUAGE plpgsql;

Answer 2

你确定问题出在你引用的部分吗？我刚刚创建了一个精简版的函数，它很有效。你正在试用什么版本的Postgres？也许有些功能在你的版本中不起作用（我在9.2上测试过）

我测试的内容：

DROP FUNCTION IF EXISTS permission_cache_update(int4[]);
CREATE FUNCTION permission_cache_update(
  IN affected_user_list INT4[]
)
  RETURNS TABLE(user_id INT4, user_id2 INT4)
AS
  $BODY$
  BEGIN
    RETURN QUERY
    WITH
    affected_user AS (
      SELECT unnest(affected_user_list) AS user_id
    ),
    foo as (select * from affected_user)
    select au.*, foo.*
    from affected_user au
    inner join foo using (user_id);
  END;
  $BODY$
LANGUAGE plpgsql VOLATILE;
select * from permission_cache_update(array[1,2,3]::int4[]);
 user_id | user_id2 
---------+----------
       1 |        1
       2 |        2
       3 |        3