From 23f11dc21b0135702a2852aac927bdc4f9d69cef Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Tue, 24 May 2016 13:30:40 -0400
Subject: [PATCH] In examples of Oracle PL/SQL code, use varchar2 not varchar.

Oracle recommends using VARCHAR2 not VARCHAR, allegedly because they might
someday change VARCHAR to be spec-compliant about distinguishing null from
empty string.  (I'm not holding my breath, though.)  Our examples of PL/SQL
code were using VARCHAR, which while not wrong is missing the pedagogical
opportunity to talk about converting Oracle type names to Postgres.  So
switch the examples to use VARCHAR2, and add some text about what to do
with common Oracle type names like VARCHAR2 and NUMBER.  (There is probably
more to be said here, but those are the ones I'm sure about offhand.)
Per suggestion from rapg12@gmail.com.

Discussion: <20160521140046.22591.24672@wrigleys.postgresql.org>
---
 doc/src/sgml/plpgsql.sgml | 38 +++++++++++++++++++++++++++++---------
 1 file changed, 29 insertions(+), 9 deletions(-)

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 6da87b5e73..7f23c2f9bc 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -4916,6 +4916,17 @@ CREATE FUNCTION
       </para>
      </listitem>
 
+     <listitem>
+      <para>
+       Data type names often need translation.  For example, in Oracle string
+       values are commonly declared as being of type <type>varchar2</>, which
+       is a non-SQL-standard type.  In <productname>PostgreSQL</productname>,
+       use type <type>varchar</> or <type>text</> instead.  Similarly, replace
+       type <type>number</> with <type>numeric</>, or use some other numeric
+       data type if there's a more appropriate one.
+      </para>
+     </listitem>
+
      <listitem>
       <para>
        Instead of packages, use schemas to organize your functions
@@ -4977,9 +4988,9 @@ CREATE FUNCTION
     <para>
      Here is an <productname>Oracle</productname> <application>PL/SQL</> function:
 <programlisting>
-CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
-                                                  v_version varchar)
-RETURN varchar IS
+CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2,
+                                                  v_version varchar2)
+RETURN varchar2 IS
 BEGIN
     IF v_version IS NULL THEN
         RETURN v_name;
@@ -4996,6 +5007,15 @@ show errors;
      <application>PL/pgSQL</>:
 
      <itemizedlist>
+      <listitem>
+       <para>
+        The type name <type>varchar2</> has to be changed to <type>varchar</>
+        or <type>text</>.  In the examples in this section, we'll
+        use <type>varchar</>, but <type>text</> is often a better choice if
+        you do not need specific string length limits.
+       </para>
+      </listitem>
+
       <listitem>
        <para>
         The <literal>RETURN</literal> key word in the function
@@ -5071,8 +5091,8 @@ CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
         ORDER BY try_order;
     func_cmd VARCHAR(4000);
 BEGIN
-    func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR,
-                 v_domain IN VARCHAR, v_url IN VARCHAR) RETURN VARCHAR IS BEGIN';
+    func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR2,
+                 v_domain IN VARCHAR2, v_url IN VARCHAR2) RETURN VARCHAR2 IS BEGIN';
 
     FOR referrer_key IN referrer_keys LOOP
         func_cmd := func_cmd ||
@@ -5167,10 +5187,10 @@ $func$ LANGUAGE plpgsql;
      This is the Oracle version:
 <programlisting>
 CREATE OR REPLACE PROCEDURE cs_parse_url(
-    v_url IN VARCHAR,
-    v_host OUT VARCHAR,  -- This will be passed back
-    v_path OUT VARCHAR,  -- This one too
-    v_query OUT VARCHAR) -- And this one
+    v_url IN VARCHAR2,
+    v_host OUT VARCHAR2,  -- This will be passed back
+    v_path OUT VARCHAR2,  -- This one too
+    v_query OUT VARCHAR2) -- And this one
 IS
     a_pos1 INTEGER;
     a_pos2 INTEGER;
-- 
2.40.0