{"id":9110,"date":"2019-02-03T21:09:49","date_gmt":"2019-02-03T21:09:49","guid":{"rendered":"https:\/\/dbtut.com\/?p=9110"},"modified":"2019-02-03T21:12:14","modified_gmt":"2019-02-03T21:12:14","slug":"postgresql-psql-gexec","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/","title":{"rendered":"PostgreSQL \u2013 psql \u2013 gexec"},"content":{"rendered":"

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.<\/p>\n

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.<\/p>\n

So, if you try the following on a client other than psql, you probably won’t get results.<\/p>\n

First,\u00a0write a sql that produces sql.\u00a0This will create 5 tables. It is good to check before executing.<\/p>\n

SELECT format(\n'CREATE TABLE gexec_test_%s (\nid integer,\nsome_data text,\nprimary key (id)\n);', gs\n)\nFROM generate_series(1, 5) gs;<\/pre>\n
\"\"<\/p>\n
Let’s create them. All we have to do is add \\gexec to the end of the query we wrote above.<\/p>\n
\"\"<\/p>\n
Our tables are created. We can see the result with \\dt<\/code>.<\/p>\n
\"\"<\/p>\n
Let’s drop them.<\/p>\n
SELECT format('drop table public.%I;', tablename)\nFROM pg_tables\nWHERE schemaname = 'public' AND tablename LIKE 'gexec_test_%';<\/pre>\n
\"\"<\/p>\n
We checked the output first. Then we execute it and saw the result. Very easy\/practical.<\/p>\n
Notes<\/strong><\/p>\n
    \n
  • Queries are executed in the order of output and if they have multiple columns in the output, they are executed from left to right.<\/li>\n
  • Queries to be run must have SQL queries. The meta commands for psql cannot be used.<\/li>\n
  • 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.<\/li>\n<\/ul>\n\n
    <\/div>\n
    <\/path><\/svg><\/i> \"Loading\"<\/p>\n
    <\/div>\n","protected":false},"excerpt":{"rendered":"
    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 …<\/p>\n
    <\/div>\n
    <\/path><\/svg><\/i> \"Loading\"<\/p>\n
    <\/div>\n","protected":false},"author":384,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[5],"tags":[1906],"class_list":["post-9110","post","type-post","status-publish","format-standard","","category-postgres","tag-gexec-in-postgresql"],"aioseo_notices":[],"yoast_head":"\nPostgreSQL \u2013 psql \u2013 gexec - Database Tutorials<\/title>\n<meta name=\"description\" content=\"PostgreSQL \u2013 psql \u2013 gexec\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL \u2013 psql \u2013 gexec - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL \u2013 psql \u2013 gexec\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-02-03T21:09:49+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-02-03T21:12:14+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c5756f27bec6.png\" \/>\n<meta name=\"author\" content=\"\u015eahap A\u015f\u00e7\u0131\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"\u015eahap A\u015f\u00e7\u0131\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/\"},\"author\":{\"name\":\"\u015eahap A\u015f\u00e7\u0131\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/a2e613d6be4a3553f844a52366dc0888\"},\"headline\":\"PostgreSQL \u2013 psql \u2013 gexec\",\"datePublished\":\"2019-02-03T21:09:49+00:00\",\"dateModified\":\"2019-02-03T21:12:14+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/\"},\"wordCount\":260,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c5756f27bec6.png\",\"keywords\":[\"gexec in postgresql\"],\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/\",\"name\":\"PostgreSQL \u2013 psql \u2013 gexec - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c5756f27bec6.png\",\"datePublished\":\"2019-02-03T21:09:49+00:00\",\"dateModified\":\"2019-02-03T21:12:14+00:00\",\"description\":\"PostgreSQL \u2013 psql \u2013 gexec\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c5756f27bec6.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c5756f27bec6.png\",\"width\":426,\"height\":643},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL \u2013 psql \u2013 gexec\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/a2e613d6be4a3553f844a52366dc0888\",\"name\":\"\u015eahap A\u015f\u00e7\u0131\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/005a983c36f6cca1fef1bc16f5d9d1d3f2fd7a27692b5d2c362b0b806bcfdb4a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/005a983c36f6cca1fef1bc16f5d9d1d3f2fd7a27692b5d2c362b0b806bcfdb4a?s=96&d=mm&r=g\",\"caption\":\"\u015eahap A\u015f\u00e7\u0131\"},\"url\":\"https:\/\/dbtut.com\/index.php\/author\/sahapasci\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PostgreSQL \u2013 psql \u2013 gexec - Database Tutorials","description":"PostgreSQL \u2013 psql \u2013 gexec","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:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL \u2013 psql \u2013 gexec - Database Tutorials","og_description":"PostgreSQL \u2013 psql \u2013 gexec","og_url":"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/","og_site_name":"Database Tutorials","article_published_time":"2019-02-03T21:09:49+00:00","article_modified_time":"2019-02-03T21:12:14+00:00","og_image":[{"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c5756f27bec6.png","type":"","width":"","height":""}],"author":"\u015eahap A\u015f\u00e7\u0131","twitter_card":"summary_large_image","twitter_misc":{"Written by":"\u015eahap A\u015f\u00e7\u0131","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/"},"author":{"name":"\u015eahap A\u015f\u00e7\u0131","@id":"https:\/\/dbtut.com\/#\/schema\/person\/a2e613d6be4a3553f844a52366dc0888"},"headline":"PostgreSQL \u2013 psql \u2013 gexec","datePublished":"2019-02-03T21:09:49+00:00","dateModified":"2019-02-03T21:12:14+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/"},"wordCount":260,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c5756f27bec6.png","keywords":["gexec in postgresql"],"articleSection":["PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/","url":"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/","name":"PostgreSQL \u2013 psql \u2013 gexec - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c5756f27bec6.png","datePublished":"2019-02-03T21:09:49+00:00","dateModified":"2019-02-03T21:12:14+00:00","description":"PostgreSQL \u2013 psql \u2013 gexec","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c5756f27bec6.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c5756f27bec6.png","width":426,"height":643},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/03\/postgresql-psql-gexec\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL \u2013 psql \u2013 gexec"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/a2e613d6be4a3553f844a52366dc0888","name":"\u015eahap A\u015f\u00e7\u0131","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/005a983c36f6cca1fef1bc16f5d9d1d3f2fd7a27692b5d2c362b0b806bcfdb4a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/005a983c36f6cca1fef1bc16f5d9d1d3f2fd7a27692b5d2c362b0b806bcfdb4a?s=96&d=mm&r=g","caption":"\u015eahap A\u015f\u00e7\u0131"},"url":"https:\/\/dbtut.com\/index.php\/author\/sahapasci\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/9110","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/384"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=9110"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/9110\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=9110"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=9110"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=9110"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}