Dynamic SQL (EXECUTE) as condition for IF statement

postgresql dynamic-sql plpgsql
Matt picture Matt · Dec 9, 2011 · Viewed 27.9k times · Source

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?

Answer

Erwin Brandstetter picture Erwin Brandstetter · Dec 9, 2011

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 just simplified. For dynamic SQL executed with EXECUTE, read the manual here. You can check for FOUND after RETURN QUERY EXECUTE:

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';

   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 ...

Related questions

Truncating all tables in a Postgres database

I regularly need to delete all the data from my PostgreSQL database before a rebuild. How would I do this directly in SQL? At the moment I've managed to come up with a SQL statement that returns all the commands …

Read More
Table name as a PostgreSQL function parameter

I want to pass a table name as a parameter in a Postgres function. I tried this code: CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer AS $$ BEGIN IF EXISTS (select * from quote_ident($1) where quote_ident($1).…

Read More
PostgreSQL - Writing dynamic sql in stored procedure that returns a result set

How can I write a stored procedure that contains a dynamically built SQL statement that returns a result set? Here is my sample code: CREATE OR REPLACE FUNCTION reporting.report_get_countries_new ( starts_with varchar, ends_with varchar ) RETURNS …

Read More