CODEWITHSUNDEEP
sqlpostgresqlstored-proceduresplpgsql
Kohányi Róbert
Kohányi Róbert

Reputation: 10131

PL/pgSQL : can't use variable on the right side of `in` operator

I'm writing a function using PostgreSQL 10 and I can't figure out how to extract a sub-query I use into a variable. My code looks like this

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")
    ...

It works as expected (you can try it here).

My question is, how could extract the sub-query on the right side of in into a variable in a way similar to this

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
       ...

The difference is that I try to declare a variable to which I want to assign the result of the sub-query (a list/array/collection integers) and then I use this on the right side of the in operator.

This last version doesn't work, I get SQL Error [42601]: ERROR: syntax error at or near "block" .... Here you can see it in action.

How could I make this work? What am I doing wrong? Is there a solution?

My goal is to reuse the result of the sub-query later on, to avoid typing the same-query multiple times and to understand why I couldn't do this. I also tried different data types in the declaration: any, %ROWTYPE, but I just couldn't understand what's going on.

Upvotes: 3

Views: 256

Answers (1)

klin
klin

Reputation: 121604

The variable schedule_ids is an array, you should assign an array value to it using array_agg(). Next, use ANY (array expression) instead of IN operator:

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.

Upvotes: 4

Related Questions