Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I want to execute a dynamic SQL statement, with its returned value being the conditional for an IF statement:

IF EXECUTE 'EXISTS (SELECT 1 FROM mytable)' THEN
This generates the error ERROR:  type "execute" does not exist.
Is it possible to do this, or is it necessary to execute the SQL before the IF statement into a variable, and then check the variable as the conditional?
This construct is not possible:
IF EXECUTE 'EXISTS (SELECT 1 FROM mytable)' THEN ...
You can simplify to:
IF EXISTS (SELECT 1 FROM mytable) THEN ...
But your example is probably simplified. For dynamic SQL executed with EXECUTE, read the manual here. You can check the special variable FOUND immediately after executing any DML command to see whether any rows here affected:
IF FOUND THEN ...
However:
Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.
Bold emphasis mine. For a plain EXECUTE do this instead:
DECLARE
   i int;
BEGIN
   EXECUTE 'SELECT 1 FROM mytable';  -- something dynamic here
   GET DIAGNOSTICS i = ROW_COUNT;
   IF i > 0 THEN ...
Or if opportune - in particular with only single-row results - use the INTO clause with EXECUTE to get a result from the dynamic query directly. I quote the manual here:
If a row or variable list is provided, it must exactly match the
structure of the query's results (when a record variable is used, it
will configure itself to match the result structure automatically). If
multiple rows are returned, only the first will be assigned to the
INTO variable. 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 ...
Matt,
From the syntax above, you're writing PL/pgSQL, not SQL. On tht assumption, there are two ways to do what you want, but both will require two lines of code:
EXECUTE 'SELECT EXISTS (SELECT 1 FROM ' || table_variable || ' );' INTO boolean_var;
IF boolean_var THEN ...
EXECUTE 'SELECT 1 FROM ' || table_variable || ' );';
IF FOUND THEN ...
"FOUND" is a special variable which checks if the last query run returned any rows.
                You are mistaken concerning FOUND. It is not set by a plain EXECUTE. See my answer for details.
– Erwin Brandstetter
                Dec 9, 2011 at 17:57
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.