A look at PostgreSQL 9.6 – psql gexec

This little, but powerful, enhancement that it made it in PostgreSQL 9.6 is probably nothing a application can benefit of. But it can be huge saving for people doing maintenance operations on a PostgreSQL instance. It is the addition of the “gexec” meta command to psql. If you want to read where this comes from and how it evolved you can read the mail thread on hackers.

As always lets do a little demo. Lets say you have a simple sql that produces some “create table” statements:

select 'create table tt'||gen||' (a int, b int )'||';'
  from generate_series(1,20) gen;

When you run this the output will be:

              ?column?              
------------------------------------
 create table tt1 (a int, b int );
 create table tt2 (a int, b int );
 create table tt3 (a int, b int );
 create table tt4 (a int, b int );
 create table tt5 (a int, b int );
 create table tt6 (a int, b int );
 create table tt7 (a int, b int );
 create table tt8 (a int, b int );
 create table tt9 (a int, b int );
 create table tt10 (a int, b int );
 create table tt11 (a int, b int );
 create table tt12 (a int, b int );
 create table tt13 (a int, b int );
 create table tt14 (a int, b int );
 create table tt15 (a int, b int );
 create table tt16 (a int, b int );
 create table tt17 (a int, b int );
 create table tt18 (a int, b int );
 create table tt19 (a int, b int );
 create table tt20 (a int, b int );

If you now want to execute the generated statements you could get rid of the heading and all other formatting and spool it into a file. You then can execute the file and you are done. You could even do that in one script all together. Now, with gexec, there is much more elegant way to do this:

select 'create table tt'||gen||' (a int, b int )' 
  from generate_series(1,20) gen
gexec

What this does is: Each generated command from the select is executed in the order the statement returns them:

postgres@[local]:5432) [postgres] > select 'create table tt'||gen||' (a int, b int )' 
                                      from generate_series(1,20) gen
gexec
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE

All the tables are there. You can image yourself what you could do with that 🙂 While reading the documentation I additionally learned about the format function today. If you, for example, want to create an index on every column of the above tables you could do:

with tabs as
( select 'tt'||a tab
    from generate_series(1,20) a
)
select format('create index on %I',b.tab)||format('(%I)' ,c.attname)
  from tabs b
     , pg_attribute c
 where c.attrelid = b.tab::varchar::regclass
   and c.attnum > 0
  order by 1
gexec
CREATE INDEX
CREATE INDEX
...
CREATE INDEX
CREATE INDEX

Easy and fast. Of course you can drop the tables the same way:

select 'drop table tt'||gen
  from generate_series(1,20) gen
gexec

Really nice feature. Have fun …