Save execute results into a table-CodePudding

Below is a simplified postgres stored procedure I am trying to run:

create or replace procedure my_schema.tst(suffix varchar) 
as $$
    
begin
    
    execute('   select *        
                into my_schema.MyTable_'||suffix||'
                From my_schema.MyTable
                '); 

end;
$$
language plpgsql;

When I attempt to run using something like:

call my_schema.tst('test');

I get this error Invalid operation: EXECUTE of SELECT ... INTO is not supported;

Is it possible to execute a dynamic query that creates a new table? I have seen examples that look like:

Execute('... some query ...') into Table;

but for my use case I need the resulting tablename to be passed as a variable.

CodePudding user response：

In PostgreSQL you can use INSERT INTO tname SELECT...

create or replace procedure my_schema.tst(suffix varchar) 
as $$
    
begin
    
    execute '  INSERT INTO my_schema.MyTable_'||suffix||' SELECT *        
                 FROM my_schema.MyTable
                '; 

end;
$$
language plpgsql;

or Use CREATE TABLE tname AS SELECT..., :

create or replace procedure my_schema.tst(suffix varchar) 
as $$
    
begin
    
    execute '  CREATE TABLE my_schema.MyTable_'||suffix||' as SELECT *        
                 FROM my_schema.MyTable
                '; 

end;
$$
language plpgsql;