How to execute the case in the request, you need to put it in a function?

case when (select "set" from "OrderList" where id_order=8 and id = 2)=true then (delete from "orderList" where id_order=8 and id_parents = 2) (update "OrderList" set id_parents = 0,hash_product='hash_product1','name_product1',id_type_parent=2,name_type_parent='name_type_parent1',id_type=2,name_type = 'sadasd',parameters=array[2,3,1,4,6,4,3],"set"=false,finished=false where id_order=8 and id = 2) else (update "OrderList" set id_parents = 0,hash_product='hash_product1','name_product1',id_type_parent=2,name_type_parent='name_type_parent1',id_type=2,name_type = 'sadasd',parameters=array[2,3,1,4,6,4,3],"set"=false,finished=false where id_order=8 and id = 2) end
  • 2
    Yes, in the function / procedure and use not case, but IF from pl / sql - Mike
  • one
    case - put in select and assumes a "scalar" result on both the three inputs and the output. If - nick_n_a is used in the program stream

2 answers 2

For those who are used to imperative programming, it will be easier to use the stored procedure. You don’t need to return anything from their functions, so for this you don’t even have to create a storage explicitly, postgresql can do anonymous DO code blocks:

 do language plpgsql $func$ begin if exists (select 1 from "OrderList" where id_order=8 and id = 2 and "set"=true) then delete from "orderList" where id_order=8 and id_parents = 2; update "OrderList" set id_parents = 0,hash_product='hash_product1','name_product1',id_type_parent=2,name_type_parent='name_type_parent1',id_type=2,name_type = 'sadasd',parameters=array[2,3,1,4,6,4,3],"set"=false,finished=false where id_order=8 and id = 2; else update "OrderList" set id_parents = 0,hash_product='hash_product1','name_product1',id_type_parent=2,name_type_parent='name_type_parent1',id_type=2,name_type = 'sadasd',parameters=array[2,3,1,4,6,4,3],"set"=false,finished=false where id_order=8 and id = 2 end if; end $func$

Either use declarative SQL logic and describe what should work. You have, perhaps by mistake, the update in both branches of the condition is identical. The task can be solved in a universal way to do anything with one request - through CTE.

 with orderset_exists as ( select id from "OrderList" where id_order=8 and id = 2 and "set" = true ), orderset_delete as ( delete from "orderList" where id_order=8 and id_parents in (select id from orderset_exists) ) update "OrderList" set id_parents = 0,hash_product='hash_product1','name_product1',id_type_parent=2,name_type_parent='name_type_parent1',id_type=2,name_type = 'sadasd',parameters=array[2,3,1,4,6,4,3],"set"=false,finished=false where id_order=8 and id = 2
  • WITH I like what I need, thank you. with orderset_exists as ( select id from "OrderList" where id_order=8 and id = 2 and "set" = true ), orderset_delete as ( delete from "OrderList" where id_order=8 and id_parents in (select id from orderset_exists) ) update "OrderList" set id_parents = 0,hash_product='hash_product1',name_product='name_product1',id_type_parent=2,name_type_parent='name_type_parent1',id_type=2,name_type = 'sadasd',parameters=array[2,3,1,4,6,4,3],"set"=false,finished=false where id_order=8 and id = 2 - Ghost

declare id number; begin select "set" into id from "OrderList" where id_order=8 and id = 2; delete from "orderList" where id_order=8 and id_parents = 2; update "OrderList" set id_parents = 0,hash_product='hash_product1' ... exception when no_data_found then update "OrderList" set id_parents = 0,hash_product='hash_product1' ... end;