时间戳

首页 > 解决方案 > PostgreSQL 类型为 table/setof 的数组

sql - PostgreSQL 类型为 table/setof 的数组

问题描述

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

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

例如：

特殊列名为“watchers”，有两个变体：“my_team”和“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$

标签： sqlarrayspostgresqlwhere-clausesql-function

解决方案

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

假设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')

推荐阅读