gpt4 book ai didi

postgresql - plgpsql - 是否可以声明 setof 记录或表变量?

转载 作者:行者123 更新时间:2023-11-29 11:37:05 27 4
gpt4 key购买 nike

我有以下 - 不工作 - 功能:

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) SELECT user_id, permission_id FROM current_relations WHERE NOT EXIST (SELECT user_id, permission_id FROM permission_cache);

我认为可以通过表 -> 二维数组转换,但这很复杂,所以如果可以使用记录,那会更好......

解决方案:

用一维数组创建循环比使用记录或二维数组更容易:

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 中的自动格式化不是很好...:S

最佳答案

我认为这个简化的SQL 函数 可能会满足您的需求:

CREATE FUNCTION permission_cache_update(affected_user_list int[])
RETURNS void AS
$func$

DELETE FROM permission_cache p
USING (SELECT unnest($1) AS u_id) a
WHERE p.user_id = a.u_id;

INSERT INTO permission_cache (user_id, permission_id)
SELECT u.user_id, r.permission_id
FROM user_role u
JOIN role_permission r USING (role_id)
JOIN (SELECT unnest($1) AS u_id) a ON a.u_id = u.user_id
UNION
SELECT p.user_id, p.permission_id
FROM user_permission p
JOIN (SELECT unnest($1) AS u_id) a ON a.u_id = p.user_id;

$func$ LANGUAGE sql;

Writable CTE (a.k.a. data-modifying CTE)会极大地简化这个,但只在 Postgres 9.1 中引入。再一次,您的过时版本妨碍了您。

按名称引用参数仅在 Postgres 9.2 中引入用于 SQL 函数(早期用于 plpgsql)。所以我使用位置参数 $1

关于postgresql - plgpsql - 是否可以声明 setof 记录或表变量?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/17848680/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com