Dynamic DDL

Performance Snippets

Dynamic DDL Execution

Works with PostgreSQL

>=7.4

Written in

PL/pgSQL

Depends on

Nothing

In many examples in this section, an SQL query produces as output a list of DDL statements to be subsequently executed. This is a fairly common administrative requirement.

The following trivial function allows such queries to be executed directly with no intermediate step. Doing this also takes advantage of PostgreSQL's ability to do transactional DDL; either all changes are made, or all are rolled back. Of course, for safety one would typically do an explicit BEGIN; first, and inspect the list of commands and, if necessary, the resulting changes to the database, before doing a COMMIT;.

create function exec(text) returns text language plpgsql volatile
  as $f$
    begin
      execute $1;
      return $1;
    end;
$f$;

The format() function (available in PostgreSQL 9.1+) is handy to construct dynamic statements. The following function combines exec(format()) into a single function:

create function exec(text, variadic anyarray) returns text language plpgsql volatile
  as $f$
    DECLARE
      cmd text;
    BEGIN
      cmd := format($1, variadic $2);
      EXECUTE cmd;
      RETURN cmd;
    END;
$f$;

Note that "variadic" expects at least one argument, so you will need both of the above functions for full flexibility.