store result of execute string into temp table

Question

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.

GMB · Accepted Answer · 2020-04-21T17:14:25.967

0

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.

edited Apr 21 '20 at 17:14

answered Apr 21 '20 at 17:10

GMB

216,147
25
84
135

will i be able to use the table further in my function for queries that are present variables like ```SQLSTR```? – Jay Mehta Apr 21 '20 at 17:14
@JayMehta: yes. The temporary table survives until the end of the database session that created it. – GMB Apr 21 '20 at 17:15

store result of execute string into temp table

1 Answers1