As database administrators or software developers, we sometimes write sqls that produce sqls. We save the output to the file. Then we run this file again and get the actual result we want. If the generated script is not too large, we can complete the process with the copy / paste method.
Previously, I also saw functions that were written in order to solve this situation practically. The gexec command in psql that comes with PostgreSQL 9.6 makes this process very easy. In fact, it is a meta-command that is not in the syntax of PostgreSQL but is in the client application psql.
So, if you try the following on a client other than psql, you probably won’t get results.
First, write a sql that produces sql. This will create 5 tables. It is good to check before executing.
|
1 2 3 4 5 6 7 8 |
SELECT format( 'CREATE TABLE gexec_test_%s ( id integer, some_data text, primary key (id) );', gs ) FROM generate_series(1, 5) gs; |
Let’s create them. All we have to do is add \gexec to the end of the query we wrote above.
Our tables are created. We can see the result with \dt.
Let’s drop them.
|
1 2 3 |
SELECT format('drop table public.%I;', tablename) FROM pg_tables WHERE schemaname = 'public' AND tablename LIKE 'gexec_test_%'; |
We checked the output first. Then we execute it and saw the result. Very easy/practical.
Notes
- Queries are executed in the order of output and if they have multiple columns in the output, they are executed from left to right.
- Queries to be run must have SQL queries. The meta commands for psql cannot be used.
- By default, execution continues if an error occurs in a query. If you want to avoid this, you can also use the ON_ERROR_STOP parameter of psql.
