{"id":40639,"date":"2025-10-09T13:17:35","date_gmt":"2025-10-09T11:17:35","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=40639"},"modified":"2025-10-09T13:17:37","modified_gmt":"2025-10-09T11:17:37","slug":"from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\/","title":{"rendered":"From Oracle\u2019s PL\/SQL to PostgreSQL\u2019s PL\/pgSQL \u2013 2 \u2013 Records, outputs and constraints"},"content":{"rendered":"\n<p>In the <a href=\"https:\/\/www.dbi-services.com\/blog\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-1-basics\/\" target=\"_blank\" rel=\"noreferrer noopener\">last post <\/a>we&#8217;ve learned the basics for migrating a sample procedure from PL\/SQL to PL\/pgSQL. In this post we&#8217;ll actually start with migrating the code. The first step in the original procedure was to iterate over the foreign key constraints of the user&#8217;s tables and to disable them:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\n  for i in ( select table_name\n                  , constraint_name\n               from user_constraints\n              where constraint_type =&#039;R&#039;\n                and status = &#039;ENABLED&#039; )\n  loop\n    execute immediate &#039;alter table &#039; || i.table_name || &#039; disable constraint &#039; || i.constraint_name;\n  end loop;\n  dbms_output.put_line ( &#039;Disabled foreign key constraints&#039;);\n<\/pre><\/div>\n\n\n<p>If you try this as it is in PL\/pgSQL this will fail for several reasons:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=&gt; \\! cat a.sql\ncreate or replace procedure p1() as\n$$\ndeclare\nbegin \n  for i in ( select table_name\n                  , constraint_name\n               from user_constraints\n              where constraint_type =&#039;R&#039;\n                and status = &#039;ENABLED&#039; )\n  loop\n    execute immediate &#039;alter table &#039; || i.table_name || &#039; disable constraint &#039; || i.constraint_name;\n  end loop;\n  dbms_output.put_line ( &#039;Disabled foreign key constraints&#039;);\n\nend;\n$$ language plpgsql;\npostgres=&gt; \\i a.sql\npsql:a.sql:16: ERROR:  loop variable of loop over rows must be a record variable or list of scalar variables\nLINE 5:   for i in ( select table_name\n<\/pre><\/div>\n\n\n<p>The error message is pretty clear, we need to use a variable of type &#8220;record&#8221; if we want to iterate over a number of rows, so:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [5]; title: ; notranslate\" title=\"\">\npostgres=&gt; \\! cat a.sql\ncreate or replace procedure p1() as\n$$\ndeclare\n  i record;\nbegin \n  for i in ( select table_name\n                  , constraint_name\n               from user_constraints\n              where constraint_type =&#039;R&#039;\n                and status = &#039;ENABLED&#039; )\n  loop\n    execute immediate &#039;alter table &#039; || i.table_name || &#039; disable constraint &#039; || i.constraint_name;\n  end loop;\n  dbms_output.put_line ( &#039;Disabled foreign key constraints&#039;);\n\nend;\n$$ language plpgsql;\npostgres=&gt; \\i a.sql\npsql:a.sql:17: ERROR:  syntax error at or near &quot;dbms_output&quot;\nLINE 14:   dbms_output.put_line ( &#039;Disabled foreign key constraints&#039;...\n<\/pre><\/div>\n\n\n<p>dbms_output is an Oracle package, this does not exist in PostgreSQL. We have two options:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Create a schema called &#8220;dbms_output&#8221; and in there create a procedure &#8220;put_line&#8221; which does what we want<\/li>\n\n\n\n<li>Use what PL\/pgSQL provides by default, and this is <a href=\"https:\/\/www.postgresql.org\/docs\/current\/plpgsql-errors-and-messages.html\">RAISE<\/a><\/li>\n<\/ul>\n\n\n\n<p>(There is a third option, which is <a href=\"https:\/\/github.com\/orafce\/orafce\" target=\"_blank\" rel=\"noreferrer noopener\">orafce<\/a>, but we&#8217;re not going to look into this one)<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [15]; title: ; notranslate\" title=\"\">\npostgres=&gt; \\! cat a.sql\ncreate or replace procedure p1() as\n$$\ndeclare\n  i record;\nbegin \n  for i in ( select table_name\n                  , constraint_name\n               from user_constraints\n              where constraint_type =&#039;R&#039;\n                and status = &#039;ENABLED&#039; )\n  loop\n    execute immediate &#039;alter table &#039; || i.table_name || &#039; disable constraint &#039; || i.constraint_name;\n  end loop;\n  raise notice &#039;Disabled foreign key constraints&#039;;\n\nend;\n$$ language plpgsql;\npostgres=&gt; \\i a.sql\nCREATE PROCEDURE\n<\/pre><\/div>\n\n\n<p>Now the procedure can be created, but there are more issues to fix. If we execute the procedure we get this:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=&gt; call p1();\nERROR:  relation &quot;user_constraints&quot; does not exist\nLINE 3:                from user_constraints\n                            ^\nQUERY:  ( select table_name\n                  , constraint_name\n               from user_constraints\n              where constraint_type =&#039;R&#039;\n                and status = &#039;ENABLED&#039; )\nCONTEXT:  PL\/pgSQL function p1() line 5 at FOR over SELECT rows\n<\/pre><\/div>\n\n\n<p>&#8220;user_constraints&#8221; is an Oracle catalog view, and this view is of course not existing in PostgreSQL. But PostgreSQL provides the catalog table <a href=\"https:\/\/www.postgresql.org\/docs\/current\/catalog-pg-constraint.html\">pg_constraint<\/a>, so:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=&gt; \\! cat a.sql\ncreate or replace procedure p1() as\n$$\ndeclare\n  i record;\nbegin \n  for i in ( select conrelid::regclass as table_name\n                  , conname as constraint_name\n               from pg_constraint\n              where contype =&#039;f&#039; )\n  loop\n    execute immediate &#039;alter table &#039; || i.table_name || &#039; disable constraint &#039; || i.constraint_name;\n  end loop;\n  raise notice &#039;Disabled foreign key constraints&#039;;\n\nend;\n$$ language plpgsql;\npostgres=&gt; \\i a.sql\nCREATE PROCEDURE\npostgres=&gt; call p1();\nERROR:  type &quot;immediate&quot; does not exist\nLINE 1: immediate &#039;alter table &#039; || i.table_name || &#039; disable constr...\n        ^\nQUERY:  immediate &#039;alter table &#039; || i.table_name || &#039; disable constraint &#039; || i.constraint_name\nCONTEXT:  PL\/pgSQL function p1() line 10 at EXECUTE\n<\/pre><\/div>\n\n\n<p>Next issue: &#8220;IMMEDIATE&#8221; does not exist in PL\/pgSQL, but this is easy to fix, just remove it:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=&gt; \\! cat a.sql\ncreate or replace procedure p1() as\n$$\ndeclare\n  i record;\nbegin \n  for i in ( select conrelid::regclass as table_name\n                  , conname as constraint_name\n               from pg_constraint\n              where contype =&#039;f&#039; )\n  loop\n    execute &#039;alter table &#039; || i.table_name || &#039; disable constraint &#039; || i.constraint_name;\n  end loop;\n  raise notice &#039;Disabled foreign key constraints&#039;;\n\nend;\n$$ language plpgsql;\npostgres=&gt; \\i a.sql\nCREATE PROCEDURE\npostgres=&gt; call p1();\nERROR:  syntax error at or near &quot;constraint&quot;\nLINE 1: alter table t2 disable constraint t2_b_fkey\n                               ^\nQUERY:  alter table t2 disable constraint t2_b_fkey\nCONTEXT:  PL\/pgSQL function p1() line 10 at EXECUTE\n<\/pre><\/div>\n\n\n<p>In PostgreSQL you <a href=\"https:\/\/www.dbi-services.com\/blog\/enabling-disabling-and-validating-foreign-key-constraints-in-postgresql\/\" target=\"_blank\" rel=\"noreferrer noopener\">cannot disable constraints<\/a> as you can do it in Oracle:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=# alter table t2 disable \nROW LEVEL SECURITY  RULE                TRIGGER    \n<\/pre><\/div>\n\n\n<p>The way to do this in PostgreSQL is to drop the constraints and re-create them afterwards but for this we need to store the &#8220;create&#8221; commands. One way of doing that would be something like this:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\ncreate or replace procedure p1() as\n$$\ndeclare\n  i record;\n  constraint_create text&#x5B;];\n  tmp text;\n  tmp2 text;\n  counter int := 1;\nbegin\n  for i in ( select oid\n                  , conrelid::regclass as table_name\n                  , conname as constraint_name\n               from pg_constraint\n              where contype =&#039;f&#039; )\n  loop\n    select &#039;alter table &#039; || i.table_name || &#039; add constraint &#039; || i.constraint_name || &#039; &#039; || pg_get_constraintdef ( i.oid ) into tmp;\n    constraint_create&#x5B;counter] := tmp;\n    execute &#039;alter table &#039; || i.table_name || &#039; drop constraint &#039; || i.constraint_name;\n    counter := counter + 1;\n  end loop;\n  raise notice &#039;Dropped foreign key constraints&#039;;\n  -- load data\n  -- re-create the foreign keys\n  foreach tmp2 in array constraint_create\n  loop\n    raise notice &#039;Re-creating foreign key constraint: %&#039;, tmp2;\n    execute tmp2;\n  end loop;\nend;\n$$ language plpgsql;\n<\/pre><\/div>\n\n\n<p>This almost completes the migration of the code but there is still the topic of &#8220;exceptions&#8221;, and that&#8217;s for the next post.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In the last post we&#8217;ve learned the basics for migrating a sample procedure from PL\/SQL to PL\/pgSQL. In this post we&#8217;ll actually start with migrating the code. The first step in the original procedure was to iterate over the foreign key constraints of the user&#8217;s tables and to disable them: If you try this as [&hellip;]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[77],"type_dbi":[],"class_list":["post-40639","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>From Oracle\u2019s PL\/SQL to PostgreSQL\u2019s PL\/pgSQL \u2013 2 \u2013 Records, outputs and constraints - 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\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"From Oracle\u2019s PL\/SQL to PostgreSQL\u2019s PL\/pgSQL \u2013 2 \u2013 Records, outputs and constraints\" \/>\n<meta property=\"og:description\" content=\"In the last post we&#8217;ve learned the basics for migrating a sample procedure from PL\/SQL to PL\/pgSQL. In this post we&#8217;ll actually start with migrating the code. The first step in the original procedure was to iterate over the foreign key constraints of the user&#8217;s tables and to disable them: If you try this as [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2025-10-09T11:17:35+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-10-09T11:17:37+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\\\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"From Oracle\u2019s PL\\\/SQL to PostgreSQL\u2019s PL\\\/pgSQL \u2013 2 \u2013 Records, outputs and constraints\",\"datePublished\":\"2025-10-09T11:17:35+00:00\",\"dateModified\":\"2025-10-09T11:17:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\\\/\"},\"wordCount\":312,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\\\/\",\"name\":\"From Oracle\u2019s PL\\\/SQL to PostgreSQL\u2019s PL\\\/pgSQL \u2013 2 \u2013 Records, outputs and constraints - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2025-10-09T11:17:35+00:00\",\"dateModified\":\"2025-10-09T11:17:37+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"From Oracle\u2019s PL\\\/SQL to PostgreSQL\u2019s PL\\\/pgSQL \u2013 2 \u2013 Records, outputs and constraints\"}]},{\"@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":"From Oracle\u2019s PL\/SQL to PostgreSQL\u2019s PL\/pgSQL \u2013 2 \u2013 Records, outputs and constraints - 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\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\/","og_locale":"en_US","og_type":"article","og_title":"From Oracle\u2019s PL\/SQL to PostgreSQL\u2019s PL\/pgSQL \u2013 2 \u2013 Records, outputs and constraints","og_description":"In the last post we&#8217;ve learned the basics for migrating a sample procedure from PL\/SQL to PL\/pgSQL. In this post we&#8217;ll actually start with migrating the code. The first step in the original procedure was to iterate over the foreign key constraints of the user&#8217;s tables and to disable them: If you try this as [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\/","og_site_name":"dbi Blog","article_published_time":"2025-10-09T11:17:35+00:00","article_modified_time":"2025-10-09T11:17:37+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\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"From Oracle\u2019s PL\/SQL to PostgreSQL\u2019s PL\/pgSQL \u2013 2 \u2013 Records, outputs and constraints","datePublished":"2025-10-09T11:17:35+00:00","dateModified":"2025-10-09T11:17:37+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\/"},"wordCount":312,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\/","url":"https:\/\/www.dbi-services.com\/blog\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\/","name":"From Oracle\u2019s PL\/SQL to PostgreSQL\u2019s PL\/pgSQL \u2013 2 \u2013 Records, outputs and constraints - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2025-10-09T11:17:35+00:00","dateModified":"2025-10-09T11:17:37+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/from-oracles-pl-sql-to-postgresqls-pl-pgsql-2-records-outputs-and-constraints\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"From Oracle\u2019s PL\/SQL to PostgreSQL\u2019s PL\/pgSQL \u2013 2 \u2013 Records, outputs and constraints"}]},{"@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\/40639","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=40639"}],"version-history":[{"count":19,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/40639\/revisions"}],"predecessor-version":[{"id":40792,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/40639\/revisions\/40792"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=40639"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=40639"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=40639"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=40639"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}