类型为table/setof的PostgreSQL数组

Question

我需要在postgresql中编写一个函数，该函数必须根据条件从表中"拾取"整行，然后将它们插入到数组中，最后返回该数组。

我实际上是在查询我需要的行，但表中有一个特殊的列，我必须首先评估它，看看我是否可以检索它(插入到数组中(，这取决于执行查询的用户类型(客户或工作人员(。

例如：

该特殊列被命名为"；观察者"；并且有两个变体："my_team"one_answers"only_me"，并且还有另一个名为"的列；user_id"；。

如果列显示"my_team"，则该行可以毫无问题地插入到数组中，但如果它显示"only_me"，则我必须比较调用函数的用户和该行中注册的用户的id，如果它们匹配，则我可以将其插入到数组，否则我不能。

问题是，正如我到目前为止所读到的，它似乎不能用数组来完成，所以我想知道是否有其他方法可以做到这一点，也许用一个额外的表或其他什么？

谢谢你的帮助！

这是我的代码：

CREATE OR REPLACE FUNCTION public.calculates_permission_by_task(task_id integer)
RETURNS SETOF permission
LANGUAGE plpgsql
STABLE
AS $function$
DECLARE 
num int := 5;
record permission;
ret_permissions permission [];
BEGIN
FOR record IN (select
p.id, p.slug
from task as t
join service_request_form as srf on t.service_request_form_id = srf.id
join service_group as sg on srf.service_group_id = sg.id
join worker_group as wg on wg.service_group_id = sg.id
join worker_group_member as wgm on wg.id = wgm.worker_group_id
join role as r on r.id = wgm.role_id
join permission_role as pr on r.id = pr.role_id
join permission as p on p.id = pr.permission_id
where t.id = task_id
and wgm.user_id = 'LFudaU6jzid4SKFlU8MgFAwezyP2'
and pr.value <> 'off'
and pr.value <> 'no')
LOOP
/* Here is where I pretend to do the comparison and insert data into the array */
ret_permission := array_append(ret_permissions, record.id);
END LOOP;

RETURN ret_permissions;

END
$function$

Answer 1

您可以在查询本身中实现逻辑。

假设您引用的user_id列对应于查询中已经存在的wgm.user_id，则查询的where子句将变为：

where 
t.id = task_id
and pr.value not in ('off', 'no')
and (
?.watchers = 'my_team'
or (
?.watchers = 'only_me'
or wgm.user_id = 'LFudaU6jzid4SKFlU8MgFAwezyP2'
)
)

您不知道watches属于哪个表，所以我使用了?，您需要将其替换为相关的表别名。

如果watchers只有两个可能的值，那么我们可以简化谓词：

where 
t.id = task_id
and pr.value not in ('off', 'no')
and (?.watchers = 'my_team' or wgm.user_id = 'LFudaU6jzid4SKFlU8MgFAwezyP2')

Answer 2

• 您不需要数组
• 您不需要游标循环
• 您甚至不需要plpgsql
• 纯SQL就足够了：

---

CREATE TABLE  permission (
id integer
, slug text
);
INSERT INTO permission(id,slug) VALUES (1,'WTF' );
CREATE TABLE  task (
id  integer
, service_request_form_id  integer
);
INSERT INTO task(id, service_request_form_id) VALUES (1,1 );
CREATE TABLE  service_request_form (
id integer
, service_group_id  integer
);
INSERT INTO service_request_form(id, service_group_id) VALUES (1,1 );
CREATE TABLE  service_group (
id integer
);
INSERT INTO service_group(id) VALUES (1);
CREATE TABLE  worker_group (
id  integer
, service_group_id  integer
);
INSERT INTO worker_group(id, service_group_id) VALUES (1,1 );
CREATE TABLE  worker_group_member (
worker_group_id integer
, role_id integer
, user_id text
);
INSERT INTO worker_group_member(worker_group_id, role_id,user_id) VALUES (1,1,'LFudaU6jzid4SKFlU8MgFAwezyP2' );
CREATE TABLE  zrole (
id  integer
);
INSERT INTO zrole(id) VALUES (1 );
CREATE TABLE  permission_role (
role_id integer
, permission_id integer
, value text
);
INSERT INTO permission_role(role_id,permission_id,value) VALUES (1,1,'Yes' );
CREATE OR REPLACE FUNCTION public.calculates_permission_by_task(task_id integer)
RETURNS SETOF permission
LANGUAGE sql STABLE SECURITY INVOKER ROWS 30 -- COST 1
AS $func$
SELECT p.id, p.slug
FROM permission as p
WHERE EXISTS (
SELECT *
FROM task as t
JOIN service_request_form as srf
on t.service_request_form_id = srf.id
JOIN service_group as sg
on srf.service_group_id = sg.id
JOIN worker_group as wg
on wg.service_group_id = sg.id
JOIN worker_group_member as wgm
on wg.id = wgm.worker_group_id
JOIN zrole as zr
on zr.id = wgm.role_id
JOIN permission_role as pr
on zr.id = pr.role_id
WHERE p.id = pr.permission_id
AND t.id = task_id
and wgm.user_id = 'LFudaU6jzid4SKFlU8MgFAwezyP2'
and pr.value NOT IN( 'off' , 'no' )
-- -------------------------
-- Add the needed logic
-- (From @GMB) here
-- -------------------------
);
$func$
;

-- Usage:
-- --------------
SELECT * -- p.id, p.slug
FROM public.calculates_permission_by_task(42);