CODEWITHSUNDEEP
sqlpostgresql
imantha
imantha

Reputation: 3828

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

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;

Upvotes: 0

Views: 1325

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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.

Upvotes: 1

Related Questions