Available in: PSQL

Added in: 1.5

Description

EXECUTE STATEMENT takes a single string argument and executes it as if it had been submitted as a DSQL statement. The exact syntax depends on the number of data rows that the supplied statement may return.

This form is used with INSERT, UPDATE, DELETE and EXECUTE PROCEDURE statements that return no data.

Syntax

EXECUTE STATEMENT <statement>

<statement> ::= An SQL statement returning no data.

Example

create procedure DynamicSampleOne (ProcName varchar(100))
as
declare variable stmt varchar(1024);
declare variable param int;
begin
   select min(SomeField) from SomeTable into param;
   stmt = 'execute procedure '
          || ProcName
          || '('
          || cast(param as varchar(20))
          || ')';
   execute statement stmt;
end

Warning: Although this form of EXECUTE STATEMENT can also be used with all kinds of DDL strings (except CREATE/DROP DATABASE), it is generally very, very unwise to use this trick in order to circumvent the no-DDL rule in PSQL.

This form is used with singleton SELECT statements.

Syntax

EXECUTE STATEMENT <select-statement> INTO <var> [, <var> ...]

<select-statement> ::= An SQL statement returning at most one row of data.
<var>              ::= A PSQL variable, optionally preceded by “:”

Example

create procedure DynamicSampleTwo (TableName varchar(100))
as
declare variable param int;
begin
   execute statement
      'select max(CheckField) from ' || TableName into :param;
   if (param > 100) then
      exception Ex_Overflow 'Overflow in ' || TableName;
end

This form – analogous to FOR SELECT … DO – is used with SELECT statements that may return a multi-row dataset.

Syntax

FOR EXECUTE STATEMENT <select-statement> INTO <var> [, <var> ...]
  DO <compound-statement>

<select-statement> ::= Any SELECT statement.
<var>              ::= A PSQL variable, optionally preceded by “:”

Example

create procedure DynamicSampleThree
   ( TextField varchar(100),
     TableName varchar(100) )
returns
   ( LongLine varchar(32000) )
as
declare variable Chunk varchar(100); 
begin
   Chunk = '';
   for execute statement
     'select ' || TextField || ' from ' || TableName into :Chunk
   do
     if (Chunk is not null) then
       LongLine = LongLine || Chunk || ' ';
   suspend;
end

1. There is no way to validate the syntax of the enclosed statement.
2. There are no dependency checks to discover whether tables or columns have been dropped.
3. Operations will be slow because the embedded statement has to be prepared every time it is executed.
4. The argument string cannot contain any parameters. All variable substitution into the static part of the DSQL statement should be performed before EXECUTE STATEMENT is called.
5. Return values are strictly checked for data type in order to avoid unpredictable type-casting exceptions. For example, the string '1234' would convert to an integer, 1234, but 'abc' would give a conversion error.
6. The submitted DSQL statement is always executed with the privileges of the current user. Privileges granted to the trigger or stored procedure that contains the EXECUTE STATEMENT statement are not in effect while the DSQL statement runs.

All in all, this feature is intended only for very cautious use and you should always take the above factors into account. Bottom line: use EXECUTE STATEMENT only when other methods are impossible, or perform even worse than EXECUTE STATEMENT.