{"id":7934,"date":"2016-05-19T05:04:44","date_gmt":"2016-05-19T03:04:44","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/"},"modified":"2016-05-19T05:04:44","modified_gmt":"2016-05-19T03:04:44","slug":"a-look-at-postgresql-9-6-psql-gexec-2","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/","title":{"rendered":"A look at PostgreSQL 9.6 \u2013 psql gexec"},"content":{"rendered":"<p>This little, but powerful, enhancement that it made it in <a href=\"http:\/\/www.postgresql.org\/docs\/9.6\/static\/release-9-6.html\" target=\"_blank\" rel=\"noopener\">PostgreSQL 9.6<\/a> 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 &#8220;gexec&#8221; meta command to <a href=\"http:\/\/www.postgresql.org\/docs\/9.6\/static\/app-psql.html\" target=\"_blank\" rel=\"noopener\">psql<\/a>. If you want to read where this comes from and how it evolved you can read the <a href=\"http:\/\/www.postgresql.org\/message-id\/CADkLM=exRzVQu31kjaBPzpbu_rGUTtWDTNELNysg1ChEPSpDMQ@mail.gmail.com\">mail thread on hackers<\/a>.<\/p>\n<p><!--more--><\/p>\n<p>As always lets do a little demo. Lets say you have a simple sql that produces some &#8220;create table&#8221; statements:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nselect 'create table tt'||gen||' (a int, b int )'||';'\n  from generate_series(1,20) gen;\n<\/pre>\n<p>When you run this the output will be:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n              ?column?              \n------------------------------------\n create table tt1 (a int, b int );\n create table tt2 (a int, b int );\n create table tt3 (a int, b int );\n create table tt4 (a int, b int );\n create table tt5 (a int, b int );\n create table tt6 (a int, b int );\n create table tt7 (a int, b int );\n create table tt8 (a int, b int );\n create table tt9 (a int, b int );\n create table tt10 (a int, b int );\n create table tt11 (a int, b int );\n create table tt12 (a int, b int );\n create table tt13 (a int, b int );\n create table tt14 (a int, b int );\n create table tt15 (a int, b int );\n create table tt16 (a int, b int );\n create table tt17 (a int, b int );\n create table tt18 (a int, b int );\n create table tt19 (a int, b int );\n create table tt20 (a int, b int );\n<\/pre>\n<p>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:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nselect 'create table tt'||gen||' (a int, b int )' \n  from generate_series(1,20) gen\ngexec\n<\/pre>\n<p>What this does is: Each generated command from the select is executed in the order the statement returns them:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres@[local]:5432) [postgres] &gt; select 'create table tt'||gen||' (a int, b int )' \n                                      from generate_series(1,20) gen\ngexec\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\n<\/pre>\n<p>All the tables are there. You can image yourself what you could do with that \ud83d\ude42 While reading the documentation I additionally learned about the <a href=\"\" target=\"_blank\" rel=\"noopener\">format function<\/a> today. If you, for example, want to create an index on every column of the above tables you could do:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nwith tabs as\n( select 'tt'||a tab\n    from generate_series(1,20) a\n)\nselect format('create index on %I',b.tab)||format('(%I)' ,c.attname)\n  from tabs b\n     , pg_attribute c\n where c.attrelid = b.tab::varchar::regclass\n   and c.attnum &gt; 0\n  order by 1\ngexec\nCREATE INDEX\nCREATE INDEX\n...\nCREATE INDEX\nCREATE INDEX\n<\/pre>\n<p>Easy and fast. Of course you can drop the tables the same way:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nselect 'drop table tt'||gen\n  from generate_series(1,20) gen\ngexec\n<\/pre>\n<p>Really nice feature. Have fun &#8230;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 &#8220;gexec&#8221; meta command to psql. If you want to read where this comes from [&hellip;]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-7934","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>A look at PostgreSQL 9.6 \u2013 psql gexec - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A look at PostgreSQL 9.6 \u2013 psql gexec\" \/>\n<meta property=\"og:description\" content=\"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 &#8220;gexec&#8221; meta command to psql. If you want to read where this comes from [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-05-19T03:04:44+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"A look at PostgreSQL 9.6 \u2013 psql gexec\",\"datePublished\":\"2016-05-19T03:04:44+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/\"},\"wordCount\":241,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/\",\"name\":\"A look at PostgreSQL 9.6 \u2013 psql gexec - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-05-19T03:04:44+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A look at PostgreSQL 9.6 \u2013 psql gexec\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"A look at PostgreSQL 9.6 \u2013 psql gexec - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/","og_locale":"en_US","og_type":"article","og_title":"A look at PostgreSQL 9.6 \u2013 psql gexec","og_description":"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 &#8220;gexec&#8221; meta command to psql. If you want to read where this comes from [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/","og_site_name":"dbi Blog","article_published_time":"2016-05-19T03:04:44+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"A look at PostgreSQL 9.6 \u2013 psql gexec","datePublished":"2016-05-19T03:04:44+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/"},"wordCount":241,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/","url":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/","name":"A look at PostgreSQL 9.6 \u2013 psql gexec - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-05-19T03:04:44+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/a-look-at-postgresql-9-6-psql-gexec-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"A look at PostgreSQL 9.6 \u2013 psql gexec"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7934","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=7934"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7934\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=7934"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=7934"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=7934"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=7934"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}