PostgreSQL中fn_login返回多行时变量声明及权限校验的fn_search实现

示例表结构与数据 #

CREATE TABLE employee
(
    joining_date date,
    employee_type character varying,
    name character varying 
);
insert into employee VALUES
    (NULL,'as','hjasghg'),
    ('2022-08-12', 'Rs', 'sa'),
    (NULL,'as1','hjasghg1'),
    ('2024-09-12', 'Rs1', 'sa1'),
    (NULL,'as2','hjasghg2'),
    ('2023-12-12', 'Rs2', 'sa2'),
    (NULL,'asktyuk12','hjasg21');

疑问解答 #

1. listname应声明为text还是record？ #

既不能用text也不适合用单个record变量。因为fn_login(loginid)返回的是多行多列的结果（姓名+joining_date）：

• text只能存储单个文本值，完全无法承载多行多列的数据集；
• 单个record变量只能存储一行数据，没法容纳多行结果。

正确的做法是将listname声明为集合类型，有两种常用方式：

• 方式一：先自定义复合类型，再声明变量为该类型的集合

-- 创建自定义类型
CREATE TYPE employee_access AS (name text, joining_date date);
-- 在函数中声明变量
listname SETOF employee_access;

• 方式二：直接用TABLE语法声明变量结构

listname TABLE(name text, joining_date date);

2. 如何利用该变量过滤出当前登录ID可访问的记录？ #

因为listname是多行权限数据集，你可以通过关联查询或者IN子查询的方式，在employee表中筛选出匹配权限的记录，以下是两种实现示例：

方式一：JOIN关联过滤

将employee表与fn_login(loginid)的结果关联，只保留匹配姓名的记录：

SELECT e.employee_type, e.name
FROM employee e
JOIN fn_login(loginid) l ON e.name = l.name;

方式二：IN子查询过滤

如果仅通过姓名过滤，直接用子查询提取fn_login返回的姓名列表：

SELECT e.employee_type, e.name
FROM employee e
WHERE e.name IN (SELECT name FROM fn_login(loginid));

如果需要结合joining_date做更复杂的权限校验（比如限定日期范围），可以调整关联条件或子查询的过滤逻辑。

内容的提问来源于stack exchange，提问作者codeanonym