Update for new psql formatting.

1 files changed, 339 insertions, 275 deletions

diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out
index 703a5b22aa..d6953ab5a5 100644
--- a/src/test/regress/expected/select.out
+++ b/src/test/regress/expected/select.out
@@ -1,303 +1,367 @@
-QUERY: SELECT onek.* WHERE onek.unique1 < 10;
-unique1|unique2|two|four|ten|twenty|hundred|thousand|twothousand|fivethous|tenthous|odd|even|stringu1|stringu2|string4
--------+-------+---+----+---+------+-------+--------+-----------+---------+--------+---+----+--------+--------+-------
-      0|    998|  0|   0|  0|     0|      0|       0|          0|        0|       0|  0|   1|AAAAAA  |KMBAAA  |OOOOxx 
-      1|    214|  1|   1|  1|     1|      1|       1|          1|        1|       1|  2|   3|BAAAAA  |GIAAAA  |OOOOxx 
-      2|    326|  0|   2|  2|     2|      2|       2|          2|        2|       2|  4|   5|CAAAAA  |OMAAAA  |OOOOxx 
-      3|    431|  1|   3|  3|     3|      3|       3|          3|        3|       3|  6|   7|DAAAAA  |PQAAAA  |VVVVxx 
-      4|    833|  0|   0|  4|     4|      4|       4|          4|        4|       4|  8|   9|EAAAAA  |BGBAAA  |HHHHxx 
-      5|    541|  1|   1|  5|     5|      5|       5|          5|        5|       5| 10|  11|FAAAAA  |VUAAAA  |HHHHxx 
-      6|    978|  0|   2|  6|     6|      6|       6|          6|        6|       6| 12|  13|GAAAAA  |QLBAAA  |OOOOxx 
-      7|    647|  1|   3|  7|     7|      7|       7|          7|        7|       7| 14|  15|HAAAAA  |XYAAAA  |VVVVxx 
-      8|    653|  0|   0|  8|     8|      8|       8|          8|        8|       8| 16|  17|IAAAAA  |DZAAAA  |HHHHxx 
-      9|     49|  1|   1|  9|     9|      9|       9|          9|        9|       9| 18|  19|JAAAAA  |XBAAAA  |HHHHxx 
+--
+-- SELECT
+--
+-- btree index
+-- awk '{if($1<10){print;}else{next;}}' onek.data | sort +0n -1
+--
+SELECT onek.* WHERE onek.unique1 < 10;
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+       0 |     998 |   0 |    0 |   0 |      0 |       0 |        0 |           0 |         0 |        0 |   0 |    1 | AAAAAA   | KMBAAA   | OOOOxx
+       1 |     214 |   1 |    1 |   1 |      1 |       1 |        1 |           1 |         1 |        1 |   2 |    3 | BAAAAA   | GIAAAA   | OOOOxx
+       2 |     326 |   0 |    2 |   2 |      2 |       2 |        2 |           2 |         2 |        2 |   4 |    5 | CAAAAA   | OMAAAA   | OOOOxx
+       3 |     431 |   1 |    3 |   3 |      3 |       3 |        3 |           3 |         3 |        3 |   6 |    7 | DAAAAA   | PQAAAA   | VVVVxx
+       4 |     833 |   0 |    0 |   4 |      4 |       4 |        4 |           4 |         4 |        4 |   8 |    9 | EAAAAA   | BGBAAA   | HHHHxx
+       5 |     541 |   1 |    1 |   5 |      5 |       5 |        5 |           5 |         5 |        5 |  10 |   11 | FAAAAA   | VUAAAA   | HHHHxx
+       6 |     978 |   0 |    2 |   6 |      6 |       6 |        6 |           6 |         6 |        6 |  12 |   13 | GAAAAA   | QLBAAA   | OOOOxx
+       7 |     647 |   1 |    3 |   7 |      7 |       7 |        7 |           7 |         7 |        7 |  14 |   15 | HAAAAA   | XYAAAA   | VVVVxx
+       8 |     653 |   0 |    0 |   8 |      8 |       8 |        8 |           8 |         8 |        8 |  16 |   17 | IAAAAA   | DZAAAA   | HHHHxx
+       9 |      49 |   1 |    1 |   9 |      9 |       9 |        9 |           9 |         9 |        9 |  18 |   19 | JAAAAA   | XBAAAA   | HHHHxx
 (10 rows)
 
-QUERY: SELECT onek.unique1, onek.stringu1
-   WHERE onek.unique1 < 20
+--
+-- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1
+--
+SELECT onek.unique1, onek.stringu1
+   WHERE onek.unique1 < 20 
    ORDER BY unique1 using >;
-unique1|stringu1
--------+--------
-     19|TAAAAA  
-     18|SAAAAA  
-     17|RAAAAA  
-     16|QAAAAA  
-     15|PAAAAA  
-     14|OAAAAA  
-     13|NAAAAA  
-     12|MAAAAA  
-     11|LAAAAA  
-     10|KAAAAA  
-      9|JAAAAA  
-      8|IAAAAA  
-      7|HAAAAA  
-      6|GAAAAA  
-      5|FAAAAA  
-      4|EAAAAA  
-      3|DAAAAA  
-      2|CAAAAA  
-      1|BAAAAA  
-      0|AAAAAA  
+ unique1 | stringu1 
+---------+----------
+      19 | TAAAAA
+      18 | SAAAAA
+      17 | RAAAAA
+      16 | QAAAAA
+      15 | PAAAAA
+      14 | OAAAAA
+      13 | NAAAAA
+      12 | MAAAAA
+      11 | LAAAAA
+      10 | KAAAAA
+       9 | JAAAAA
+       8 | IAAAAA
+       7 | HAAAAA
+       6 | GAAAAA
+       5 | FAAAAA
+       4 | EAAAAA
+       3 | DAAAAA
+       2 | CAAAAA
+       1 | BAAAAA
+       0 | AAAAAA
 (20 rows)
 
-QUERY: SELECT onek.unique1, onek.stringu1
-   WHERE onek.unique1 > 980
+--
+-- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2
+--
+SELECT onek.unique1, onek.stringu1
+   WHERE onek.unique1 > 980 
    ORDER BY stringu1 using <;
-unique1|stringu1
--------+--------
-    988|AMAAAA  
-    989|BMAAAA  
-    990|CMAAAA  
-    991|DMAAAA  
-    992|EMAAAA  
-    993|FMAAAA  
-    994|GMAAAA  
-    995|HMAAAA  
-    996|IMAAAA  
-    997|JMAAAA  
-    998|KMAAAA  
-    999|LMAAAA  
-    981|TLAAAA  
-    982|ULAAAA  
-    983|VLAAAA  
-    984|WLAAAA  
-    985|XLAAAA  
-    986|YLAAAA  
-    987|ZLAAAA  
+ unique1 | stringu1 
+---------+----------
+     988 | AMAAAA
+     989 | BMAAAA
+     990 | CMAAAA
+     991 | DMAAAA
+     992 | EMAAAA
+     993 | FMAAAA
+     994 | GMAAAA
+     995 | HMAAAA
+     996 | IMAAAA
+     997 | JMAAAA
+     998 | KMAAAA
+     999 | LMAAAA
+     981 | TLAAAA
+     982 | ULAAAA
+     983 | VLAAAA
+     984 | WLAAAA
+     985 | XLAAAA
+     986 | YLAAAA
+     987 | ZLAAAA
 (19 rows)
 
-QUERY: SELECT onek.unique1, onek.string4
-   WHERE onek.unique1 > 980
+	
+--
+-- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |
+-- sort +1d -2 +0nr -1
+--
+SELECT onek.unique1, onek.string4
+   WHERE onek.unique1 > 980 
    ORDER BY string4 using <, unique1 using >;
-unique1|string4
--------+-------
-    999|AAAAxx 
-    995|AAAAxx 
-    983|AAAAxx 
-    982|AAAAxx 
-    981|AAAAxx 
-    998|HHHHxx 
-    997|HHHHxx 
-    993|HHHHxx 
-    990|HHHHxx 
-    986|HHHHxx 
-    996|OOOOxx 
-    991|OOOOxx 
-    988|OOOOxx 
-    987|OOOOxx 
-    985|OOOOxx 
-    994|VVVVxx 
-    992|VVVVxx 
-    989|VVVVxx 
-    984|VVVVxx 
+ unique1 | string4 
+---------+---------
+     999 | AAAAxx
+     995 | AAAAxx
+     983 | AAAAxx
+     982 | AAAAxx
+     981 | AAAAxx
+     998 | HHHHxx
+     997 | HHHHxx
+     993 | HHHHxx
+     990 | HHHHxx
+     986 | HHHHxx
+     996 | OOOOxx
+     991 | OOOOxx
+     988 | OOOOxx
+     987 | OOOOxx
+     985 | OOOOxx
+     994 | VVVVxx
+     992 | VVVVxx
+     989 | VVVVxx
+     984 | VVVVxx
 (19 rows)
 
-QUERY: SELECT onek.unique1, onek.string4
+	
+--
+-- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |
+-- sort +1dr -2 +0n -1
+--
+SELECT onek.unique1, onek.string4
    WHERE onek.unique1 > 980
    ORDER BY string4 using >, unique1 using <;
-unique1|string4
--------+-------
-    984|VVVVxx 
-    989|VVVVxx 
-    992|VVVVxx 
-    994|VVVVxx 
-    985|OOOOxx 
-    987|OOOOxx 
-    988|OOOOxx 
-    991|OOOOxx 
-    996|OOOOxx 
-    986|HHHHxx 
-    990|HHHHxx 
-    993|HHHHxx 
-    997|HHHHxx 
-    998|HHHHxx 
-    981|AAAAxx 
-    982|AAAAxx 
-    983|AAAAxx 
-    995|AAAAxx 
-    999|AAAAxx 
+ unique1 | string4 
+---------+---------
+     984 | VVVVxx
+     989 | VVVVxx
+     992 | VVVVxx
+     994 | VVVVxx
+     985 | OOOOxx
+     987 | OOOOxx
+     988 | OOOOxx
+     991 | OOOOxx
+     996 | OOOOxx
+     986 | HHHHxx
+     990 | HHHHxx
+     993 | HHHHxx
+     997 | HHHHxx
+     998 | HHHHxx
+     981 | AAAAxx
+     982 | AAAAxx
+     983 | AAAAxx
+     995 | AAAAxx
+     999 | AAAAxx
 (19 rows)
 
-QUERY: SELECT onek.unique1, onek.string4
+	
+--
+-- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |
+-- sort +0nr -1 +1d -2
+--
+SELECT onek.unique1, onek.string4
    WHERE onek.unique1 < 20
    ORDER BY unique1 using >, string4 using <;
-unique1|string4
--------+-------
-     19|OOOOxx 
-     18|VVVVxx 
-     17|HHHHxx 
-     16|OOOOxx 
-     15|VVVVxx 
-     14|AAAAxx 
-     13|OOOOxx 
-     12|AAAAxx 
-     11|OOOOxx 
-     10|AAAAxx 
-      9|HHHHxx 
-      8|HHHHxx 
-      7|VVVVxx 
-      6|OOOOxx 
-      5|HHHHxx 
-      4|HHHHxx 
-      3|VVVVxx 
-      2|OOOOxx 
-      1|OOOOxx 
-      0|OOOOxx 
+ unique1 | string4 
+---------+---------
+      19 | OOOOxx
+      18 | VVVVxx
+      17 | HHHHxx
+      16 | OOOOxx
+      15 | VVVVxx
+      14 | AAAAxx
+      13 | OOOOxx
+      12 | AAAAxx
+      11 | OOOOxx
+      10 | AAAAxx
+       9 | HHHHxx
+       8 | HHHHxx
+       7 | VVVVxx
+       6 | OOOOxx
+       5 | HHHHxx
+       4 | HHHHxx
+       3 | VVVVxx
+       2 | OOOOxx
+       1 | OOOOxx
+       0 | OOOOxx
 (20 rows)
 
-QUERY: SELECT onek.unique1, onek.string4
-   WHERE onek.unique1 < 20
+--
+-- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |
+-- sort +0n -1 +1dr -2
+--
+SELECT onek.unique1, onek.string4
+   WHERE onek.unique1 < 20 
    ORDER BY unique1 using <, string4 using >;
-unique1|string4
--------+-------
-      0|OOOOxx 
-      1|OOOOxx 
-      2|OOOOxx 
-      3|VVVVxx 
-      4|HHHHxx 
-      5|HHHHxx 
-      6|OOOOxx 
-      7|VVVVxx 
-      8|HHHHxx 
-      9|HHHHxx 
-     10|AAAAxx 
-     11|OOOOxx 
-     12|AAAAxx 
-     13|OOOOxx 
-     14|AAAAxx 
-     15|VVVVxx 
-     16|OOOOxx 
-     17|HHHHxx 
-     18|VVVVxx 
-     19|OOOOxx 
+ unique1 | string4 
+---------+---------
+       0 | OOOOxx
+       1 | OOOOxx
+       2 | OOOOxx
+       3 | VVVVxx
+       4 | HHHHxx
+       5 | HHHHxx
+       6 | OOOOxx
+       7 | VVVVxx
+       8 | HHHHxx
+       9 | HHHHxx
+      10 | AAAAxx
+      11 | OOOOxx
+      12 | AAAAxx
+      13 | OOOOxx
+      14 | AAAAxx
+      15 | VVVVxx
+      16 | OOOOxx
+      17 | HHHHxx
+      18 | VVVVxx
+      19 | OOOOxx
 (20 rows)
 
-QUERY: SELECT two, stringu1, ten, string4
+--
+-- partial btree index
+-- awk '{if($1<10){print $0;}else{next;}}' onek.data | sort +0n -1
+--
+--SELECT onek2.* WHERE onek2.unique1 < 10;
+--
+-- partial btree index
+-- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1
+--
+--SELECT onek2.unique1, onek2.stringu1
+--    WHERE onek2.unique1 < 20 
+--    ORDER BY unique1 using >;
+--
+-- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2
+--
+--SELECT onek2.unique1, onek2.stringu1
+--   WHERE onek2.unique1 > 980
+--   ORDER BY stringu1 using <;
+	
+SELECT two, stringu1, ten, string4
    INTO TABLE tmp
    FROM onek;
-QUERY: SELECT p.name, p.age FROM person* p;
-name   |age
--------+---
-mike   | 40
-joe    | 20
-sally  | 34
-sandra | 19
-alex   | 30
-sue    | 50
-denise | 24
-sarah  | 88
-teresa | 38
-nan    | 28
-leah   | 68
-wendy  | 78
-melissa| 28
-joan   | 18
-mary   |  8
-jane   | 58
-liza   | 38
-jean   | 28
-jenifer| 38
-juanita| 58
-susan  | 78
-zena   | 98
-martie | 88
-chris  | 78
-pat    | 18
-zola   | 58
-louise | 98
-edna   | 18
-bertha | 88
-sumi   | 38
-koko   | 88
-gina   | 18
-rean   | 48
-sharon | 78
-paula  | 68
-julie  | 68
-belinda| 38
-karen  | 48
-carina | 58
-diane  | 18
-esther | 98
-trudy  | 88
-fanny  |  8
-carmen | 78
-lita   | 25
-pamela | 48
-sandy  | 38
-trisha | 88
-vera   | 78
-velma  | 68
-sharon | 25
-sam    | 30
-bill   | 20
-fred   | 28
-larry  | 60
-jeff   | 23
-cim    | 30
-linda  | 19
+--
+-- awk '{print $1,$2;}' person.data |
+-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data |
+-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data |
+-- awk 'BEGIN{FS="      ";}{if(NF!=2){print $4,$5;}else{print;}}' - stud_emp.data
+--
+-- SELECT name, age FROM person*; ??? check if different
+SELECT p.name, p.age FROM person* p;
+  name   | age 
+---------+-----
+ mike    |  40
+ joe     |  20
+ sally   |  34
+ sandra  |  19
+ alex    |  30
+ sue     |  50
+ denise  |  24
+ sarah   |  88
+ teresa  |  38
+ nan     |  28
+ leah    |  68
+ wendy   |  78
+ melissa |  28
+ joan    |  18
+ mary    |   8
+ jane    |  58
+ liza    |  38
+ jean    |  28
+ jenifer |  38
+ juanita |  58
+ susan   |  78
+ zena    |  98
+ martie  |  88
+ chris   |  78
+ pat     |  18
+ zola    |  58
+ louise  |  98
+ edna    |  18
+ bertha  |  88
+ sumi    |  38
+ koko    |  88
+ gina    |  18
+ rean    |  48
+ sharon  |  78
+ paula   |  68
+ julie   |  68
+ belinda |  38
+ karen   |  48
+ carina  |  58
+ diane   |  18
+ esther  |  98
+ trudy   |  88
+ fanny   |   8
+ carmen  |  78
+ lita    |  25
+ pamela  |  48
+ sandy   |  38
+ trisha  |  88
+ vera    |  78
+ velma   |  68
+ sharon  |  25
+ sam     |  30
+ bill    |  20
+ fred    |  28
+ larry   |  60
+ jeff    |  23
+ cim     |  30
+ linda   |  19
 (58 rows)
 
-QUERY: SELECT p.name, p.age FROM person* p ORDER BY age using >, name;
-name   |age
--------+---
-esther | 98
-louise | 98
-zena   | 98
-bertha | 88
-koko   | 88
-martie | 88
-sarah  | 88
-trisha | 88
-trudy  | 88
-carmen | 78
-chris  | 78
-sharon | 78
-susan  | 78
-vera   | 78
-wendy  | 78
-julie  | 68
-leah   | 68
-paula  | 68
-velma  | 68
-larry  | 60
-carina | 58
-jane   | 58
-juanita| 58
-zola   | 58
-sue    | 50
-karen  | 48
-pamela | 48
-rean   | 48
-mike   | 40
-belinda| 38
-jenifer| 38
-liza   | 38
-sandy  | 38
-sumi   | 38
-teresa | 38
-sally  | 34
-alex   | 30
-cim    | 30
-sam    | 30
-fred   | 28
-jean   | 28
-melissa| 28
-nan    | 28
-lita   | 25
-sharon | 25
-denise | 24
-jeff   | 23
-bill   | 20
-joe    | 20
-linda  | 19
-sandra | 19
-diane  | 18
-edna   | 18
-gina   | 18
-joan   | 18
-pat    | 18
-fanny  |  8
-mary   |  8
+--
+-- awk '{print $1,$2;}' person.data |
+-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data |
+-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data |
+-- awk 'BEGIN{FS="      ";}{if(NF!=1){print $4,$5;}else{print;}}' - stud_emp.data |
+-- sort +1nr -2
+--
+SELECT p.name, p.age FROM person* p ORDER BY age using >, name;
+  name   | age 
+---------+-----
+ esther  |  98
+ louise  |  98
+ zena    |  98
+ bertha  |  88
+ koko    |  88
+ martie  |  88
+ sarah   |  88
+ trisha  |  88
+ trudy   |  88
+ carmen  |  78
+ chris   |  78
+ sharon  |  78
+ susan   |  78
+ vera    |  78
+ wendy   |  78
+ julie   |  68
+ leah    |  68
+ paula   |  68
+ velma   |  68
+ larry   |  60
+ carina  |  58
+ jane    |  58
+ juanita |  58
+ zola    |  58
+ sue     |  50
+ karen   |  48
+ pamela  |  48
+ rean    |  48
+ mike    |  40
+ belinda |  38
+ jenifer |  38
+ liza    |  38
+ sandy   |  38
+ sumi    |  38
+ teresa  |  38
+ sally   |  34
+ alex    |  30
+ cim     |  30
+ sam     |  30
+ fred    |  28
+ jean    |  28
+ melissa |  28
+ nan     |  28
+ lita    |  25
+ sharon  |  25
+ denise  |  24
+ jeff    |  23
+ bill    |  20
+ joe     |  20
+ linda   |  19
+ sandra  |  19
+ diane   |  18
+ edna    |  18
+ gina    |  18
+ joan    |  18
+ pat     |  18
+ fanny   |   8
+ mary    |   8
 (58 rows)