EXECUTE IMMEDIATE Statement - Oracle to PostgreSQL Migration

In Oracle, you can execute a dynamic SQL using the EXECUTE IMMEDIATE statement. In PostgreSQL, you can use the EXECUTE statement.

Oracle:

  BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE colors (name VARCHAR2(30), category VARCHAR2(30))';
  END;
  /
  /* PL/SQL procedure successfully completed.*/

PostgreSQL:

  DO $$
  BEGIN
    EXECUTE 'CREATE TABLE colors (name VARCHAR(30), category VARCHAR(30))';
  END;
  $$;

Both Oracle and PostgreSQL allow you to specify the INTO clause to assign the results of the statement:

Oracle:

  DECLARE
    cnt NUMBER(10);
  BEGIN
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM colors' INTO cnt;
  END;
  /
  /* PL/SQL procedure successfully completed.*/

PostgreSQL:

  DO $$
  DECLARE
    cnt INT;
  BEGIN
    EXECUTE 'SELECT COUNT(*) FROM colors' INTO cnt;
  END;
  $$;

Both Oracle and PostgreSQL allow you to specify the USING clause to pass the parameters. Note that Oracle uses :1, :2 for parameters in dynamic SQL strings, while PostgreSQL uses $1, $2.

Oracle:

  DECLARE
    name VARCHAR2(30) := 'White';
    category VARCHAR2(30) := 'W';
  BEGIN
    EXECUTE IMMEDIATE 'INSERT INTO colors VALUES (:1, :2)' USING name, category;
  END;
  /
  /* PL/SQL procedure successfully completed.*/

PostgreSQL:

  DO $$
  DECLARE
    name VARCHAR(30) := 'White';
    category VARCHAR(30) := 'W';
  BEGIN
    EXECUTE 'INSERT INTO colors VALUES ($1, $2)' USING name, category;
  END;
  $$;

In Oracle, the USING clause can also have OUT parameters. But this requires you to use a PL/SQL block with the INTO clause inside the dynamic SQL string rather than a SQL statement:

Oracle:

  DECLARE
    cnt NUMBER(10);
    name VARCHAR2(30) := 'White';
    category VARCHAR2(30) := 'W';
  BEGIN
    EXECUTE IMMEDIATE 'BEGIN SELECT COUNT(*) INTO :1 FROM colors WHERE name = :2 AND category = :3; END;' 
      USING OUT cnt, name, category;
  END;
  /
  /* PL/SQL procedure successfully completed.*/

In PostgreSQL, the USING clause does not support OUT parameters, and you have to remove the PL/SQL block and use the INTO clause of the EXECUTE statement:

PostgreSQL:

  DO $$
  DECLARE
    cnt INT; 
    name VARCHAR(30) := 'White';
    category VARCHAR(30) := 'W';
  BEGIN
    EXECUTE 'SELECT COUNT(*) FROM colors WHERE name = $1 AND category = $2'
      INTO cnt USING name, category;
  END;
  $$;

For more information, see Oracle to PostgreSQL Migration.