Dynamic SQL (EXECUTE) as condition for IF statement

by

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

More Related Contents:

  1. INSERT with dynamic table name in trigger function
  2. Truncating all tables in a Postgres database
  3. Execute a dynamic crosstab query
  4. Test for null in function with varying parameters
  5. Return SETOF rows from PostgreSQL function
  6. Error when setting n_distinct using a plpgsql variable
  7. Dynamically generate columns for crosstab in PostgreSQL
  8. Function to loop through and select data from multiple tables
  9. Format specifier for integer variables in format() for EXECUTE?
  10. Refactor a PL/pgSQL function to return the output of various SELECT queries
  11. What are ‘$$’ used for in PL/pgSQL
  12. The forgotten assignment operator “=” and the commonplace “:=”
  13. Define table and column names as arguments in a plpgsql function?
  14. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  15. How to set value of composite variable field using dynamic SQL
  16. How do I do large non-blocking updates in PostgreSQL?
  17. dynamic sql query in postgres
  18. User defined variables in PostgreSQL
  19. Returning from a function with OUT parameter
  20. Postgres FOR LOOP
  21. Use variable set by psql meta-command inside of DO block
  22. COPY with dynamic file name
  23. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  24. SQL Sub queries in check constraint
  25. Cursor based records in PostgreSQL
  26. ERROR: unterminated quoted string at or near
  27. Select from a table variable
  28. Hashing a String to a Numeric Value in PostgreSQL
  29. plpgsql error “RETURN NEXT cannot have a parameter in function with OUT parameters” in table-returning function
  30. Loop through columns of RECORD

Leave a Comment