{"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":28,"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 v28.8 (Yoast SEO v28.8) - 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=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\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\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/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":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","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":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"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\/4bae5e5dd8c7d48edbd948772b49b202"},"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\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_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\/28"}],"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}]}}