{"id":15220,"date":"2020-11-20T12:17:08","date_gmt":"2020-11-20T11:17:08","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-will-support-out-parameters-for-procedures\/"},"modified":"2020-11-20T12:17:08","modified_gmt":"2020-11-20T11:17:08","slug":"postgresql-14-will-support-out-parameters-for-procedures","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-will-support-out-parameters-for-procedures\/","title":{"rendered":"PostgreSQL 14 will support OUT parameters for procedures"},"content":{"rendered":"<p>Before PostgreSQL 11 there was no possibility to work with procedures in PostgreSQL, only functions have been supported. Since PostgreSQL 11, procedures are supported and many waited for that because procedures also brought transaction control (commit\/rollback), which is not possible with functions. Next year, when PostgreSQL 14 will be released there will also be support for OUT parameters. Currently only <a href=\"https:\/\/www.postgresql.org\/docs\/13\/sql-createprocedure.html\" target=\"_blank\" rel=\"noopener noreferrer\">IN, INOUT and VARIADIC are supported<\/a>. This makes PostgreSQL&#8217;s procedures more compatible to Oracle&#8217;s implementation of procedures, so lets have a look.<\/p>\n<p><!--more--><\/p>\n<p>The only way for returning something from a procedure currently is to use INOUT:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select version();\n                                                          version                                                          \n---------------------------------------------------------------------------------------------------------------------------\n PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit\n(1 row)\n\npostgres=# create table t1 ( a int, b text);\nCREATE TABLE\npostgres=# insert into t1 values (1,'aa');\nINSERT 0 1\npostgres=# create or replace procedure p1 ( p_val inout int )\npostgres-# as $$\npostgres$# declare\npostgres$# begin\npostgres$#   select a\npostgres$#     into p_val\npostgres$#    from t1;\npostgres$# end;\npostgres$# $$ language plpgsql;\nCREATE PROCEDURE\n<\/pre>\n<p>This simple procedure has one INOUT parameter and the parameter will contain the result of the select once the procedure is executed:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# call p1(5);\n p_val \n-------\n     1\n(1 row)\n<\/pre>\n<p>You can, of course, use multiple INOUT parameters as well:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create or replace procedure p1 ( p_val inout int \npostgres(#                                , p_val2 inout text)\npostgres-# as $$\npostgres$# declare\npostgres$# begin\npostgres$#   select a, b\npostgres$#     into p_val, p_val2\npostgres$#    from t1;\npostgres$# end;\npostgres$# $$ language plpgsql;\nCREATE PROCEDURE\npostgres=# call p1 (5,'ccc');\n p_val | p_val2 \n-------+--------\n     1 | aa\n(1 row)\n<\/pre>\n<p>But if you try to use an OUT parameter this will not work:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create or replace procedure p1 ( p_val out int )\npostgres-# as $$\npostgres$# declare\npostgres$# begin\npostgres$#   select a\npostgres$#     into p_val\npostgres$#    from t1;\npostgres$# end;\npostgres$# $$ language plpgsql;\nERROR:  procedures cannot have OUT arguments\nHINT:  INOUT arguments are permitted.\npostgres=# \n<\/pre>\n<p>This will change with PostgreSQL 14:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select version();\n                                                  version                                                  \n-----------------------------------------------------------------------------------------------------------\n PostgreSQL 14devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit\n(1 row)\n\npostgres=# create table t1 ( a int, b text );\nCREATE TABLE\npostgres=# insert into t1 values (1,'aaa');\nINSERT 0 1\npostgres=# create or replace procedure p1 ( p_val out int )\npostgres-# as $$\npostgres$# declare\npostgres$# begin\npostgres$#   select a\npostgres$#     into p_val\npostgres$#    from t1;\npostgres$# end;\npostgres$# $$ language plpgsql;\nCREATE PROCEDURE\n<\/pre>\n<p>For testing that you somehow need to declare a variable to hold the value, which will be returned:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# do\npostgres-# $$\npostgres$# declare \npostgres$#   n int;\npostgres$# begin\npostgres$#   call p1(n);\npostgres$#   raise notice '%', n;\npostgres$# end;\npostgres$# $$;\nNOTICE:  1\nDO\n<\/pre>\n<p>Nice.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Before PostgreSQL 11 there was no possibility to work with procedures in PostgreSQL, only functions have been supported. Since PostgreSQL 11, procedures are supported and many waited for that because procedures also brought transaction control (commit\/rollback), which is not possible with functions. Next year, when PostgreSQL 14 will be released there will also be support [&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-15220","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.5) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL 14 will support OUT parameters for procedures - 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\/postgresql-14-will-support-out-parameters-for-procedures\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 14 will support OUT parameters for procedures\" \/>\n<meta property=\"og:description\" content=\"Before PostgreSQL 11 there was no possibility to work with procedures in PostgreSQL, only functions have been supported. Since PostgreSQL 11, procedures are supported and many waited for that because procedures also brought transaction control (commit\/rollback), which is not possible with functions. Next year, when PostgreSQL 14 will be released there will also be support [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-14-will-support-out-parameters-for-procedures\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-11-20T11:17:08+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=\"2 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\\\/postgresql-14-will-support-out-parameters-for-procedures\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-will-support-out-parameters-for-procedures\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL 14 will support OUT parameters for procedures\",\"datePublished\":\"2020-11-20T11:17:08+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-will-support-out-parameters-for-procedures\\\/\"},\"wordCount\":172,\"commentCount\":1,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-will-support-out-parameters-for-procedures\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-will-support-out-parameters-for-procedures\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-will-support-out-parameters-for-procedures\\\/\",\"name\":\"PostgreSQL 14 will support OUT parameters for procedures - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2020-11-20T11:17:08+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-will-support-out-parameters-for-procedures\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-will-support-out-parameters-for-procedures\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-14-will-support-out-parameters-for-procedures\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 14 will support OUT parameters for procedures\"}]},{\"@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":"PostgreSQL 14 will support OUT parameters for procedures - 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\/postgresql-14-will-support-out-parameters-for-procedures\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 14 will support OUT parameters for procedures","og_description":"Before PostgreSQL 11 there was no possibility to work with procedures in PostgreSQL, only functions have been supported. Since PostgreSQL 11, procedures are supported and many waited for that because procedures also brought transaction control (commit\/rollback), which is not possible with functions. Next year, when PostgreSQL 14 will be released there will also be support [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-will-support-out-parameters-for-procedures\/","og_site_name":"dbi Blog","article_published_time":"2020-11-20T11:17:08+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-will-support-out-parameters-for-procedures\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-will-support-out-parameters-for-procedures\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL 14 will support OUT parameters for procedures","datePublished":"2020-11-20T11:17:08+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-will-support-out-parameters-for-procedures\/"},"wordCount":172,"commentCount":1,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-14-will-support-out-parameters-for-procedures\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-will-support-out-parameters-for-procedures\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-will-support-out-parameters-for-procedures\/","name":"PostgreSQL 14 will support OUT parameters for procedures - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-11-20T11:17:08+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-will-support-out-parameters-for-procedures\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-14-will-support-out-parameters-for-procedures\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-14-will-support-out-parameters-for-procedures\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 14 will support OUT parameters for procedures"}]},{"@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\/15220","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=15220"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15220\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=15220"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=15220"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=15220"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=15220"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}