{"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:35","modified_gmt":"2025-10-09T11:17:35","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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">(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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">&#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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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;&nbsp;<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","neve_meta_reading_time":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[4133],"tags":[],"class_list":["post-40639","post","type-post","status-publish","format-standard","hentry","category-database"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.4 (Yoast SEO v28.7) - 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 services | 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;&nbsp;\" \/>\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 services | Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/dbiservices\/\" \/>\n<meta property=\"article:published_time\" content=\"2025-10-09T11:17:35+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:site\" content=\"@dbiservices\" \/>\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\\\/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\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\"},\"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\",\"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,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"articleSection\":[\"Database\"],\"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 services | Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2025-10-09T11:17:35+00:00\",\"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 services Blog\",\"description\":\"#BeSharing\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"alternateName\":\"dbi Blog\",\"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\":\"Organization\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\",\"name\":\"dbi services\",\"alternateName\":\"dbi\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"width\":512,\"height\":512,\"caption\":\"dbi services\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/dbiservices\\\/\",\"https:\\\/\\\/x.com\\\/dbiservices\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/dbi-services\\\/\",\"https:\\\/\\\/www.youtube.com\\\/user\\\/dbiservices\"],\"description\":\"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.\",\"email\":\"info@dbi-services.com\",\"telephone\":\"+41 32 422 96 00\",\"legalName\":\"dbi services SA\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"caption\":\"Open source Team\"},\"description\":\"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/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 services | 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;&nbsp;","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 services | Blog","article_publisher":"https:\/\/www.facebook.com\/dbiservices\/","article_published_time":"2025-10-09T11:17:35+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_creator":"@dbiservices","twitter_site":"@dbiservices","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\/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":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a"},"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","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,"publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"articleSection":["Database"],"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 services | Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2025-10-09T11:17:35+00:00","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 services Blog","description":"#BeSharing","publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"alternateName":"dbi Blog","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":"Organization","@id":"https:\/\/www.dbi-services.com\/blog\/#organization","name":"dbi services","alternateName":"dbi","url":"https:\/\/www.dbi-services.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","width":512,"height":512,"caption":"dbi services"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/dbiservices\/","https:\/\/x.com\/dbiservices","https:\/\/www.linkedin.com\/company\/dbi-services\/","https:\/\/www.youtube.com\/user\/dbiservices"],"description":"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.","email":"info@dbi-services.com","telephone":"+41 32 422 96 00","legalName":"dbi services SA"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","caption":"Open source Team"},"description":"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.","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\/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\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=40639"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/40639\/revisions"}],"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"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}