时间戳

首页 > 解决方案 > PL/pgSQL：不能在“in”运算符右侧使用变量

sql - PL/pgSQL：不能在“in”运算符右侧使用变量

问题描述

我正在使用 PostgreSQL 10 编写一个函数，但我不知道如何将我使用的子查询提取到变量中。我的代码看起来像这样

create function "is_valid_slot_task"() returns trigger as $$
<<block>>
declare
    schedule_id integer;
    ...
begin
    block.schedule_id := (select distinct c."schedule_id"
                          from "slot" as s
                          join "column" as c on c."column_id" = s."column_id"
                          where s."slot_id" = new."slot_id");
    if block.schedule_id
       in
       (select distinct c."schedule_id"
        from "slot_task" as st
        join "slot" as s on s."slot_id" = st."slot_id"
        join "column" as c on c."column_id" = s."column_id"
        where st."task_id" = new."task_id")
    ...

它按预期工作（您可以在这里尝试）。

in我的问题是，如何以类似于此的方式将右侧的子查询提取到变量中

create function "is_valid_slot_task"() returns trigger as $$
<<block>>
declare
    schedule_id integer;
    schedule_ids integer[];
    ...
begin
    block.schedule_id := (select distinct c."schedule_id"
                          from "slot" as s
                          join "column" as c on c."column_id" = s."column_id"
                          where s."slot_id" = new."slot_id");
    block.schedule_ids := (select distinct c."schedule_id"
                           from "slot_task" as st
                           join "slot" as s on s."slot_id" = st."slot_id"
                           join "column" as c on c."column_id" = s."column_id"
                           where st."task_id" = new."task_id");
    if block.schedule_id
       in
       block.schedule_ids
       ...

不同之处在于我尝试声明一个变量，我想将子查询的结果分配给该变量（列表/数组/集合整数），然后我在运算符的右侧使用它in。

最后一个版本不起作用，我明白了SQL Error [42601]: ERROR: syntax error at or near "block" ...。在这里您可以看到它的实际效果。

我怎样才能使这项工作？我究竟做错了什么？有解决办法吗？

我的目标是稍后重用子查询的结果，以避免多次输入相同的查询并理解为什么我不能这样做。我还在声明中尝试了不同的数据类型：any, %ROWTYPE，但我就是不明白发生了什么。

标签： sqlpostgresqlstored-proceduresplpgsql

解决方案

该变量schedule_ids是一个数组，您应该使用 为它分配一个数组值array_agg()。接下来，使用ANY (array expression)代替IN运算符：

block.schedule_ids := (select array_agg(distinct c."schedule_id")
                       from "slot_task" as st
                       join "slot" as s on s."slot_id" = st."slot_id"
                       join "column" as c on c."column_id" = s."column_id"
                       where st."task_id" = new."task_id");
if block.schedule_id = any (block.schedule_ids)

DbFiddle。

推荐阅读