Search code examples
sqlpostgresqlstored-proceduresplpgsqlcreate-table

store result of execute string into temp table

Within a function I have created a string containing a Select Query as 

SQLSTR:='select col1,col2 from '||_param1||'_'||_param2||' where col1 like ''%'||_pram3;

What I want is to store the result of SQLSTR into temporary table as FilterTable when after I run the EXECUTE SQLSTR; command.

Solution

  • Why not just use the CTAS syntax?

    SQLSTR := 
    'create temp table FilterTable as select col1,col2 from '
        || quote_ident(_param1 || '_' || _param2) 
        ||' where col1 like ''%' || _param3 || '''';

    Note that I also added a missing closing quote at the end of the statement, and used quote_ident() for the table name, in case it contains special characters.