Oracle compatibility ... - Mailing list pgsql-patches

This week I have been to Southern Germany. This customer has sucessfully
migrated from Oracle to PostgreSQL. With the help of a gborg package
(Oracle style data dictionary) and CREATE DOMAIN they were able to run
there current application WITHOUT modification.
There was just one thing we have encountered:

SELECT * FROM X MINUS SELECT * FROM X

does not work on PostgreSQL (we use EXCEPT instead).
This small patch fixes this problem.

Maybe it improvements can be integrated into 7.6

    Regards

       Hans
*** ./doc/src/sgml/keywords.sgml.orig    2004-07-08 21:11:29.694379616 +0200
--- ./doc/src/sgml/keywords.sgml    2004-07-08 21:11:12.583980792 +0200
***************
*** 1785,1790 ****
--- 1785,1796 ----
      <entry>reserved</entry>
     </row>
     <row>
+     <entry><token>MINUS</token></entry>
+     <entry>reserved</entry>
+     <entry>reserved</entry>
+     <entry>reserved</entry>
+    </row>
+    <row>
      <entry><token>MINUTE</token></entry>
      <entry>non-reserved</entry>
      <entry>reserved</entry>
*** ./doc/src/sgml/ref/select_into.sgml.orig    2004-07-08 21:15:47.767146592 +0200
--- ./doc/src/sgml/ref/select_into.sgml    2004-07-08 21:16:17.406640704 +0200
***************
*** 27,33 ****
      [ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
      [ GROUP BY <replaceable class="PARAMETER">expression</replaceable> [, ...] ]
      [ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]
!     [ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="PARAMETER">select</replaceable> ]
      [ ORDER BY <replaceable class="PARAMETER">expression</replaceable> [ ASC | DESC | USING <replaceable
class="PARAMETER">operator</replaceable>] [, ...] ] 
      [ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]
      [ OFFSET <replaceable class="PARAMETER">start</replaceable> ]
--- 27,33 ----
      [ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
      [ GROUP BY <replaceable class="PARAMETER">expression</replaceable> [, ...] ]
      [ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]
!     [ { UNION | INTERSECT | EXCEPT | MINUS} [ ALL ] <replaceable class="PARAMETER">select</replaceable> ]
      [ ORDER BY <replaceable class="PARAMETER">expression</replaceable> [ ASC | DESC | USING <replaceable
class="PARAMETER">operator</replaceable>] [, ...] ] 
      [ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]
      [ OFFSET <replaceable class="PARAMETER">start</replaceable> ]
*** ./doc/src/sgml/ref/select.sgml.orig    2004-07-08 21:12:47.145605240 +0200
--- ./doc/src/sgml/ref/select.sgml    2004-07-08 21:15:17.869691696 +0200
***************
*** 26,32 ****
      [ WHERE <replaceable class="parameter">condition</replaceable> ]
      [ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...] ]
      [ HAVING <replaceable class="parameter">condition</replaceable> [, ...] ]
!     [ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="parameter">select</replaceable> ]
      [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable
class="parameter">operator</replaceable>] [, ...] ] 
      [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
      [ OFFSET <replaceable class="parameter">start</replaceable> ]
--- 26,32 ----
      [ WHERE <replaceable class="parameter">condition</replaceable> ]
      [ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...] ]
      [ HAVING <replaceable class="parameter">condition</replaceable> [, ...] ]
!     [ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL ] <replaceable class="parameter">select</replaceable> ]
      [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable
class="parameter">operator</replaceable>] [, ...] ] 
      [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
      [ OFFSET <replaceable class="parameter">start</replaceable> ]
***************
*** 92,99 ****
        <literal>INTERSECT</literal> operator returns all rows that are
        strictly in both result sets.  The <literal>EXCEPT</literal>
        operator returns the rows that are in the first result set but
!       not in the second.  In all three cases, duplicate rows are
!       eliminated unless <literal>ALL</literal> is specified. (See
        <xref linkend="sql-union" endterm="sql-union-title">, <xref
        linkend="sql-intersect" endterm="sql-intersect-title">, and
        <xref linkend="sql-except" endterm="sql-except-title"> below.)
--- 92,102 ----
        <literal>INTERSECT</literal> operator returns all rows that are
        strictly in both result sets.  The <literal>EXCEPT</literal>
        operator returns the rows that are in the first result set but
!       not in the second. <literal>MINUS</literal> behaves the same way
!       as <literal>EXCEPT</literal>. <literal>MINUS</literal> should
!       only be used for Oracle compatibility.  In all three cases,
!       duplicate rows are eliminated unless <literal>ALL</literal>
!       is specified. (See
        <xref linkend="sql-union" endterm="sql-union-title">, <xref
        linkend="sql-intersect" endterm="sql-intersect-title">, and
        <xref linkend="sql-except" endterm="sql-except-title"> below.)
*** ./src/backend/parser/gram.y.orig    2004-07-08 20:25:04.559784432 +0200
--- ./src/backend/parser/gram.y    2004-07-08 20:36:16.820585256 +0200
***************
*** 371,377 ****
      LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
      LOCK_P

!     MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE

      NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NOCREATEDB
      NOCREATEUSER NONE NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P
--- 371,377 ----
      LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
      LOCK_P

!     MATCH MAXVALUE MINUS MINUTE_P MINVALUE MODE MONTH_P MOVE

      NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NOCREATEDB
      NOCREATEUSER NONE NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P
***************
*** 422,428 ****
  %token <ival>    ICONST PARAM

  /* precedence: lowest to highest */
! %left        UNION EXCEPT
  %left        INTERSECT
  %left        OR
  %left        AND
--- 422,428 ----
  %token <ival>    ICONST PARAM

  /* precedence: lowest to highest */
! %left        UNION EXCEPT MINUS
  %left        INTERSECT
  %left        OR
  %left        AND
***************
*** 4799,4804 ****
--- 4799,4808 ----
                  {
                      $$ = makeSetOp(SETOP_EXCEPT, $3, $1, $4);
                  }
+             | select_clause MINUS opt_all select_clause
+                 {
+                     $$ = makeSetOp(SETOP_EXCEPT, $3, $1, $4);
+                 }
          ;

  into_clause:
***************
*** 7877,7882 ****
--- 7881,7887 ----
              | LIMIT
              | LOCALTIME
              | LOCALTIMESTAMP
+             | MINUS
              | NEW
              | NOT
              | NOWAIT
*** ./src/backend/parser/keywords.c.orig    2004-07-08 21:08:24.252571056 +0200
--- ./src/backend/parser/keywords.c    2004-07-08 21:08:48.524881104 +0200
***************
*** 195,200 ****
--- 195,201 ----
      {"lock", LOCK_P},
      {"match", MATCH},
      {"maxvalue", MAXVALUE},
+     {"minus", MINUS},
      {"minute", MINUTE_P},
      {"minvalue", MINVALUE},
      {"mode", MODE},
*** ./src/bin/psql/sql_help.h.orig    2004-07-08 21:26:33.987906104 +0200
--- ./src/bin/psql/sql_help.h    2004-07-08 21:27:58.389075176 +0200
***************
*** 367,377 ****

      { "SELECT",
        N_("retrieve rows from a table or view"),
!       N_("SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]\n    * | expression [ AS output_name ] [, ...]\n
[FROM from_item [, ...] ]\n    [ WHERE condition ]\n    [ GROUP BY expression [, ...] ]\n    [ HAVING condition [, ...]
]\n   [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]\n    [ ORDER BY expression [ ASC | DESC | USING operator ] [,
...]]\n    [ LIMIT { count | ALL } ]\n    [ OFFSET start ]\n    [ FOR UPDATE [ OF table_name [, ...] ] ]\n\nwhere
from_itemcan be one of:\n\n    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]\n    ( select )
[AS ] alias [ ( column_alias [, ...] ) ]\n    function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [,
...]| column_definition [, ...] ) ]\n    function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )\n
from_item[ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]") }, 

      { "SELECT INTO",
        N_("create a new table from the results of a query"),
!       N_("SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]\n    * | expression [ AS output_name ] [, ...]\n
INTO[ TEMPORARY | TEMP ] [ TABLE ] new_table\n    [ FROM from_item [, ...] ]\n    [ WHERE condition ]\n    [ GROUP BY
expression[, ...] ]\n    [ HAVING condition [, ...] ]\n    [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]\n    [
ORDERBY expression [ ASC | DESC | USING operator ] [, ...] ]\n    [ LIMIT { count | ALL } ]\n    [ OFFSET start ]\n
[FOR UPDATE [ OF tablename [, ...] ] ]") }, 

      { "SET",
        N_("change a run-time parameter"),
--- 367,377 ----

      { "SELECT",
        N_("retrieve rows from a table or view"),
!       N_("SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]\n    * | expression [ AS output_name ] [, ...]\n
[FROM from_item [, ...] ]\n    [ WHERE condition ]\n    [ GROUP BY expression [, ...] ]\n    [ HAVING condition [, ...]
]\n   [ { UNION | INTERSECT | EXCEPT | MINUS} [ ALL ] select ]\n    [ ORDER BY expression [ ASC | DESC | USING operator
][, ...] ]\n    [ LIMIT { count | ALL } ]\n    [ OFFSET start ]\n    [ FOR UPDATE [ OF table_name [, ...] ] ]\n\nwhere
from_itemcan be one of:\n\n    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]\n    ( select )
[AS ] alias [ ( column_alias [, ...] ) ]\n    function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [,
...]| column_definition [, ...] ) ]\n    function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )\n
from_item[ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]") }, 

      { "SELECT INTO",
        N_("create a new table from the results of a query"),
!       N_("SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]\n    * | expression [ AS output_name ] [, ...]\n
INTO[ TEMPORARY | TEMP ] [ TABLE ] new_table\n    [ FROM from_item [, ...] ]\n    [ WHERE condition ]\n    [ GROUP BY
expression[, ...] ]\n    [ HAVING condition [, ...] ]\n    [ { UNION | INTERSECT | EXCEPT | MINUS} [ ALL ] select ]\n
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]\n    [ LIMIT { count | ALL } ]\n    [ OFFSET start ]\n
 [ FOR UPDATE [ OF tablename [, ...] ] ]") }, 

      { "SET",
        N_("change a run-time parameter"),