3

Is is possible to select from execute block result? I want to perform some operation (sum etc..) from it. 

 select t1.* 
 from 
   ( execute block 
     returns ( 
       OUT_VALUE integer ) 
    as 
    begin 
    ... 
    suspend; 
  end ) t1

or 

 with   
 t1 as ( execute block ... ) 
   select * 
   from t1 
   order by 
     t1.sort_column

Neither does not work. Anyone has an advice? Thanks!

Steve88
  • 2,035
  • 3
  • 19
  • 40
  • 1
    What exactly are you trying to achieve? It is not possible to wrap an `execute block` in a select, but an `execute block` can produce a result set just like a normal select would. So depending on what you are trying to do, it is just a matter of moving all select logic **into** the `execute block`. Otherwise you will need to create a real selectable stored procedure, and use that. – Mark Rotteveel Jun 09 '16 at 12:47
  • I need that too. Goal is to run some PSQL without creating stored procedure and then select from that. – Ondřej Feb 19 '19 at 13:01

1 Answers1

2

You should create an independent stored procedure like 

create procedure proc1
returns (
  OUT_VALUE integer 
) as
begin
   ... 
  suspend; 
end

and then select on this proc

select sum(OUT_VALUE)
from proc1
JPB31
  • 108
  • 5