PostgreSQL能否实现类似SQL Server的完全内联函数？

要让你的权限计算函数实现自动内联，获得和手动内联一致的性能，只需在函数定义中添加两个关键属性：STABLE 和 LEAKPROOF，修改后的函数创建语句如下：

Create Function "Permissions: Object Access Level For Person Operation"(_User uuid, _ObjectID uuid, _Operation uuid)
Returns int
STABLE
LEAKPROOF
as
$$
Select Coalesce
(
    (
        Select Max("Levels"."Access Level") "Access Level"
        From
        (
            Select
                "User Defined Fields: Values"."Object" "Person",
                Max("Permissions: Person Operations"."Access Level") "Access Level"
            From "Permissions: Person Operations"
            Inner Join "User Defined Fields: Values" On "User Defined Fields: Values"."Value" = "Permissions: Person Operations"."Field Value"
            Where "Permissions: Person Operations"."User" In (Select _User "User" Union Select "Entries"."Group" "User" From "Users: Group Entries" "Entries" Where "Entries"."User" = _User)
            And "Permissions: Person Operations"."Operation" = _Operation
            Group By "User Defined Fields: Values"."Object"
        ) "Levels"
        Where "Levels"."Person" = _ObjectID
    ),
    (
        Select "Level"."Access Level"
        From
        (
            Select Max("Operations"."Access Level") "Access Level"
            From "Permissions: Person Operations" "Operations"
            Where "Operations"."User" In (Select _User "User" Union Select "Entries"."Group" "User" From "Users: Group Entries" "Entries" Where "Entries"."User" = _User)
            And "Operations"."Operation" = _Operation
            And "Operations"."Field Value" Is Null
        ) "Level"
    ),
    (
        Select Max("Level"."Access Level") "Access Level"
        From
        (
            Select
                "User Defined Fields: Values"."Object" "Person",
                Max("Permissions: Persons"."Access Level") "Access Level"
            From "Permissions: Persons"
            Left Join "User Defined Fields: Values" On "User Defined Fields: Values"."Value" = "Permissions: Persons"."Field Value"
            Where "Permissions: Persons"."User" In (Select _User "User" Union Select "Entries"."Group" "User" From "Users: Group Entries" "Entries" Where "Entries"."User" = _User)
            Group By "User Defined Fields: Values"."Object"
        )   "Level"
        Where "Level"."Person" = _ObjectID
    ),
    (
        Select "Level"."Access Level"
        From 
        (
            Select Coalesce(Max("Permissions"."Access Level"),
                (Select "Default Access Level" From "Permissions: Subjects" Where "Object" = 'Person'), 0) "Access Level"
            From "Permissions"
            Inner Join "Permissions: Subjects" On "Permissions"."Subject" = "Permissions: Subjects"."id"
            Where "Permissions"."User" In (Select _User "User" Union Select "Entries"."Group" "User" From "Users: Group Entries" "Entries" Where "Entries"."User" = _User)
            And "Permissions: Subjects"."Object" = 'Person'
        ) "Level"
    )
) "Access Level";
$$
language SQL;

关键属性说明 #

• STABLE：告知PostgreSQL优化器，同一事务内使用相同参数调用该函数时，返回结果固定不变。这是优化器决定内联函数的核心前提，确保内联后不会改变查询逻辑。
• LEAKPROOF：声明函数不会泄露任何参数信息（如写入日志或暴露给其他会话）。该属性会消除优化器的安全顾虑，让它更愿意在WHERE子句这类场景中内联函数。

额外优化提示 #

如果添加上述属性后仍未自动内联，可在查询中添加内联强制提示：

Select some_fields
From some_table
Where /*+ INLINE("Permissions: Object Access Level For Person Operation") */
"Permissions: Object Access Level For Person Operation"(u, some_table.some_field, o) > 0

通常STABLE + LEAKPROOF的组合已足够触发自动内联，让查询性能和手动内联时一致。

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