Postgres error : set-returning functions are not allowed in CASE

Question

I am a noob at SQL (Know the very basics) and trying to use an opensource project call GridKIT to construct a power network to japan. Whilst running GridKIT I get the following error, and have no clue as how to fix it.

 psql:src/electric-3-line.sql:255: ERROR:  set-returning functions are not allowed in CASE

 LINE 3:             case when voltage is not null then unnest(voltag...
                                                   ^
 HINT:  You might be able to move the set-returning function into a LATERAL FROM item.

The last line states to to set the returning function into LATERAL FROM item. Any ideas how to go about doing this?

The code from lines 248-255, electric-3-line for reference

 insert into line_structure (line_id, part_nr, voltage, frequency, cables, wires, num_objects, num_conflicts, num_classes)
 select line_id, generate_series(1, num_classes),
        case when voltage is not null then unnest(voltage) end,
        case when frequency is not null then unnest(frequency) end,
        case when cables is not null then unnest(cables) end,
        case when wires is not null then unnest(wires) end,
        1, array[0,0,0,0], num_classes
   from line_tags;

Please explain what you really want to do. Sample data and desired rseults would help. — Gordon Linoff
– Gordon Linoff, Commented Apr 15, 2021 at 10:30
Do you even need any case expression with not null check? Simply unnest. — jarlh
– jarlh, Commented Apr 15, 2021 at 10:30

Gordon Linoff · Accepted Answer · 2021-04-15 10:32:55Z

1

You can express the logic as:

insert into line_structure (line_id, part_nr, voltage, frequency, cables, wires, num_objects, num_conflicts, num_classes)
 select line_id, generate_series(1, num_classes),
        el_voltage, el_frequency, . . .
        1, array[0,0,0,0], num_classes
   from line_tags lt left join lateral
        unnest(voltage) el_voltage
        on true left join lateral
        unnest(frequency) el_frequency left join lateral
        . . .;

This answers the question you have asked -- which is about fixing the syntax error. I would be surprised if it does what you want, because it will create a Cartesian product of all the values. You should ask a question with sample data, desired results, and a clear explanation of what you are trying to do.

answered Apr 15, 2021 at 10:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

Or maybe imantha wants from line_tags cross join unnest(voltage, frequency, cables, wires)

Gordon Linoff Over a year ago

@a_horse_with_no_name . . . I approached the problem as a syntax error, which is what the question is asking. This fixes the syntax error, although I doubt that it is what the OP really wants. If only the OP provided that information . . . but it does request it in a new question.

imantha Over a year ago

Hi, Thanks for all the answers. I haven't worked with SQL much and was hoping there was quick fix for the problem, which seems like not the case. Since I am just using the open source project (GridKIT) and didn't write the code, I am not aware what exactly each part of the code is doing. Thanks for all the answers though. Really appreciate it

Collectives™ on Stack Overflow

Postgres error : set-returning functions are not allowed in CASE

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related