Dynamic SQL (EXECUTE) as a condition for an IF statement

Question

I want to execute a dynamic SQL statement, and its return value is conditional for the IF :

 IF EXECUTE 'EXISTS (SELECT 1 FROM mytable)' THEN 

This generates an error ERROR: type "execute" does not exist .

Is it possible to do this or is it necessary to execute SQL before the IF statement in the variable, and then check the variable as conditional?

Answer 1

This design is not possible:

IF EXECUTE 'EXISTS (SELECT 1 FROM mytable)' THEN ... hit>

You can simplify:

 IF EXISTS (SELECT 1 FROM mytable) THEN ... 

But your example is probably just simplified. For dynamic SQL executed with EXECUTE , read the manual here . You can check FOUND after RETURN QUERY EXECUTE :

 IF FOUND THEN ... 

However:

Note that EXECUTE modifies the output of GET DIAGNOSTICS , but does not alter FOUND .

My bold accent. For a simple EXECUTE do this instead:

 ... DECLARE i int; BEGIN EXECUTE 'SELECT 1 FROM mytable'; GET DIAGNOSTICS i = ROW_COUNT; IF i > 0 THEN ... 

Or , if it's convenient - in particular, with only single-line results - use the INTO clause with EXECUTE to get the result from a dynamic query directly. I quote the guide here :

If a list of rows or variables is presented, it must exactly match the structure of the query results (when a record variable is used, it will automatically adjust to match the results structure). If multiple rows are returned, only the first will be assigned to INTO . If no rows are returned, NULL is assigned to the INTO Variable (s).

 ... DECLARE _var1 int; -- init value is NULL unless instructed otherwise BEGIN EXECUTE format('SELECT var1 FROM %I WHERE x=y LIMIT 1', 'my_Table') INTO _var1; IF _var1 IS NOT NULL THEN ... 

Answer 2

Matte

From the syntax above, you write PL / pgSQL, not SQL. Under the assumption, there are two ways to do what you want, but they will require two lines of code:

 EXECUTE 'SELECT EXISTS (SELECT 1 FROM ' || table_variable || ' );' INTO boolean_var; IF boolean_var THEN ... 

Or:

 EXECUTE 'SELECT 1 FROM ' || table_variable || ' );'; IF FOUND THEN ... 

"FOUND" is a special variable that checks to see if the last query returned any rows.

Answer 3

 SET @SQLQUERY='SELECT 1 FROM mytable' EXEC (@SQLQUERY) If @@RowCount >0 THEN