← Back to branch summary

~ubuntu-branches/ubuntu/trusty/postgresql-9.3/trusty-proposed

« back to all changes in this revision

Viewing changes to src/test/regress/expected/create_view.out

  • Committer: Package Import Robot
  • Author(s): Martin Pitt
  • Date: 2014-07-24 16:13:59 UTC
  • mfrom: (1.1.8)
  • Revision ID: package-import@ubuntu.com-20140724161359-uk325qfv03euxuuh
Tags: 9.3.5-0ubuntu0.14.04.1
https://launchpad.net/bugs/1348176
* New upstream bug fix release: (LP: #1348176)
  - pg_upgrade: Users who upgraded to version 9.3 using pg_upgrade may have
    an issue with transaction information which causes VACUUM to eventually
    fail. These users should run the script provided in the release notes to
    determine if their installation is affected, and then take the remedy
    steps outlined there.
  - Various data integrity and other bug fixes.
  - Secure Unix-domain sockets of temporary postmasters started during make
    check.
    Any local user able to access the socket file could connect as the
    server's bootstrap superuser, then proceed to execute arbitrary code as
    the operating-system user running the test, as we previously noted in
    CVE-2014-0067. This change defends against that risk by placing the
    server's socket in a temporary, mode 0700 subdirectory of /tmp.
  - See release notes for details:
    http://www.postgresql.org/about/news/1534/
* Remove pg_regress patches to support --host=/path, obsolete with above
  upstream changes and not applicable any more.
* Drop tcl8.6 patch, applied upstream.
* Add missing logrotate test dependency.

Show diffs side-by-side

added added

removed removed

Lines of Context:
src/test/regress/expected/create_view.out
706
706
create view v2a as select * from (tt2 join tt3 using (b,c) join tt4 using (b)) j;
707
707
create view v3 as select * from tt2 join tt3 using (b,c) full join tt4 using (b);
708
708
select pg_get_viewdef('v1', true);
709
 
      pg_get_viewdef       
710
 
---------------------------
711
 
  SELECT tt2.b,           +
712
 
     tt3.c,               +
713
 
     tt2.a,               +
714
 
     tt3.ax               +
715
 
    FROM tt2              +
716
 
    JOIN tt3 USING (b, c);
 
709
       pg_get_viewdef        
 
710
-----------------------------
 
711
  SELECT tt2.b,             +
 
712
     tt3.c,                 +
 
713
     tt2.a,                 +
 
714
     tt3.ax                 +
 
715
    FROM tt2                +
 
716
      JOIN tt3 USING (b, c);
717
717
(1 row)
718
718
 
719
719
select pg_get_viewdef('v1a', true);
720
 
        pg_get_viewdef        
721
 
------------------------------
722
 
  SELECT j.b,                +
723
 
     j.c,                    +
724
 
     j.a,                    +
725
 
     j.ax                    +
726
 
    FROM (tt2                +
727
 
    JOIN tt3 USING (b, c)) j;
 
720
         pg_get_viewdef         
 
721
--------------------------------
 
722
  SELECT j.b,                  +
 
723
     j.c,                      +
 
724
     j.a,                      +
 
725
     j.ax                      +
 
726
    FROM (tt2                  +
 
727
      JOIN tt3 USING (b, c)) j;
728
728
(1 row)
729
729
 
730
730
select pg_get_viewdef('v2', true);
731
 
      pg_get_viewdef      
732
 
--------------------------
733
 
  SELECT tt2.b,          +
734
 
     tt3.c,              +
735
 
     tt2.a,              +
736
 
     tt3.ax,             +
737
 
     tt4.ay,             +
738
 
     tt4.q               +
739
 
    FROM tt2             +
740
 
    JOIN tt3 USING (b, c)+
741
 
    JOIN tt4 USING (b);
 
731
       pg_get_viewdef       
 
732
----------------------------
 
733
  SELECT tt2.b,            +
 
734
     tt3.c,                +
 
735
     tt2.a,                +
 
736
     tt3.ax,               +
 
737
     tt4.ay,               +
 
738
     tt4.q                 +
 
739
    FROM tt2               +
 
740
      JOIN tt3 USING (b, c)+
 
741
      JOIN tt4 USING (b);
742
742
(1 row)
743
743
 
744
744
select pg_get_viewdef('v2a', true);
745
 
      pg_get_viewdef       
746
 
---------------------------
747
 
  SELECT j.b,             +
748
 
     j.c,                 +
749
 
     j.a,                 +
750
 
     j.ax,                +
751
 
     j.ay,                +
752
 
     j.q                  +
753
 
    FROM (tt2             +
754
 
    JOIN tt3 USING (b, c) +
755
 
    JOIN tt4 USING (b)) j;
 
745
       pg_get_viewdef        
 
746
-----------------------------
 
747
  SELECT j.b,               +
 
748
     j.c,                   +
 
749
     j.a,                   +
 
750
     j.ax,                  +
 
751
     j.ay,                  +
 
752
     j.q                    +
 
753
    FROM (tt2               +
 
754
      JOIN tt3 USING (b, c) +
 
755
      JOIN tt4 USING (b)) j;
756
756
(1 row)
757
757
 
758
758
select pg_get_viewdef('v3', true);
759
 
       pg_get_viewdef        
760
 
-----------------------------
761
 
  SELECT b,                 +
762
 
     tt3.c,                 +
763
 
     tt2.a,                 +
764
 
     tt3.ax,                +
765
 
     tt4.ay,                +
766
 
     tt4.q                  +
767
 
    FROM tt2                +
768
 
    JOIN tt3 USING (b, c)   +
769
 
    FULL JOIN tt4 USING (b);
 
759
        pg_get_viewdef         
 
760
-------------------------------
 
761
  SELECT b,                   +
 
762
     tt3.c,                   +
 
763
     tt2.a,                   +
 
764
     tt3.ax,                  +
 
765
     tt4.ay,                  +
 
766
     tt4.q                    +
 
767
    FROM tt2                  +
 
768
      JOIN tt3 USING (b, c)   +
 
769
      FULL JOIN tt4 USING (b);
770
770
(1 row)
771
771
 
772
772
alter table tt2 add column d int;
773
773
alter table tt2 add column e int;
774
774
select pg_get_viewdef('v1', true);
775
 
      pg_get_viewdef       
776
 
---------------------------
777
 
  SELECT tt2.b,           +
778
 
     tt3.c,               +
779
 
     tt2.a,               +
780
 
     tt3.ax               +
781
 
    FROM tt2              +
782
 
    JOIN tt3 USING (b, c);
 
775
       pg_get_viewdef        
 
776
-----------------------------
 
777
  SELECT tt2.b,             +
 
778
     tt3.c,                 +
 
779
     tt2.a,                 +
 
780
     tt3.ax                 +
 
781
    FROM tt2                +
 
782
      JOIN tt3 USING (b, c);
783
783
(1 row)
784
784
 
785
785
select pg_get_viewdef('v1a', true);
786
 
        pg_get_viewdef        
787
 
------------------------------
788
 
  SELECT j.b,                +
789
 
     j.c,                    +
790
 
     j.a,                    +
791
 
     j.ax                    +
792
 
    FROM (tt2                +
793
 
    JOIN tt3 USING (b, c)) j;
 
786
         pg_get_viewdef         
 
787
--------------------------------
 
788
  SELECT j.b,                  +
 
789
     j.c,                      +
 
790
     j.a,                      +
 
791
     j.ax                      +
 
792
    FROM (tt2                  +
 
793
      JOIN tt3 USING (b, c)) j;
794
794
(1 row)
795
795
 
796
796
select pg_get_viewdef('v2', true);
797
 
      pg_get_viewdef      
798
 
--------------------------
799
 
  SELECT tt2.b,          +
800
 
     tt3.c,              +
801
 
     tt2.a,              +
802
 
     tt3.ax,             +
803
 
     tt4.ay,             +
804
 
     tt4.q               +
805
 
    FROM tt2             +
806
 
    JOIN tt3 USING (b, c)+
807
 
    JOIN tt4 USING (b);
 
797
       pg_get_viewdef       
 
798
----------------------------
 
799
  SELECT tt2.b,            +
 
800
     tt3.c,                +
 
801
     tt2.a,                +
 
802
     tt3.ax,               +
 
803
     tt4.ay,               +
 
804
     tt4.q                 +
 
805
    FROM tt2               +
 
806
      JOIN tt3 USING (b, c)+
 
807
      JOIN tt4 USING (b);
808
808
(1 row)
809
809
 
810
810
select pg_get_viewdef('v2a', true);
811
 
      pg_get_viewdef       
812
 
---------------------------
813
 
  SELECT j.b,             +
814
 
     j.c,                 +
815
 
     j.a,                 +
816
 
     j.ax,                +
817
 
     j.ay,                +
818
 
     j.q                  +
819
 
    FROM (tt2             +
820
 
    JOIN tt3 USING (b, c) +
821
 
    JOIN tt4 USING (b)) j;
 
811
       pg_get_viewdef        
 
812
-----------------------------
 
813
  SELECT j.b,               +
 
814
     j.c,                   +
 
815
     j.a,                   +
 
816
     j.ax,                  +
 
817
     j.ay,                  +
 
818
     j.q                    +
 
819
    FROM (tt2               +
 
820
      JOIN tt3 USING (b, c) +
 
821
      JOIN tt4 USING (b)) j;
822
822
(1 row)
823
823
 
824
824
select pg_get_viewdef('v3', true);
825
 
       pg_get_viewdef        
826
 
-----------------------------
827
 
  SELECT b,                 +
828
 
     tt3.c,                 +
829
 
     tt2.a,                 +
830
 
     tt3.ax,                +
831
 
     tt4.ay,                +
832
 
     tt4.q                  +
833
 
    FROM tt2                +
834
 
    JOIN tt3 USING (b, c)   +
835
 
    FULL JOIN tt4 USING (b);
 
825
        pg_get_viewdef         
 
826
-------------------------------
 
827
  SELECT b,                   +
 
828
     tt3.c,                   +
 
829
     tt2.a,                   +
 
830
     tt3.ax,                  +
 
831
     tt4.ay,                  +
 
832
     tt4.q                    +
 
833
    FROM tt2                  +
 
834
      JOIN tt3 USING (b, c)   +
 
835
      FULL JOIN tt4 USING (b);
836
836
(1 row)
837
837
 
838
838
alter table tt3 rename c to d;
839
839
select pg_get_viewdef('v1', true);
840
 
             pg_get_viewdef              
841
 
-----------------------------------------
842
 
  SELECT tt2.b,                         +
843
 
     tt3.c,                             +
844
 
     tt2.a,                             +
845
 
     tt3.ax                             +
846
 
    FROM tt2                            +
847
 
    JOIN tt3 tt3(ax, b, c) USING (b, c);
 
840
              pg_get_viewdef               
 
841
-------------------------------------------
 
842
  SELECT tt2.b,                           +
 
843
     tt3.c,                               +
 
844
     tt2.a,                               +
 
845
     tt3.ax                               +
 
846
    FROM tt2                              +
 
847
      JOIN tt3 tt3(ax, b, c) USING (b, c);
848
848
(1 row)
849
849
 
850
850
select pg_get_viewdef('v1a', true);
851
 
               pg_get_viewdef               
852
 
--------------------------------------------
853
 
  SELECT j.b,                              +
854
 
     j.c,                                  +
855
 
     j.a,                                  +
856
 
     j.ax                                  +
857
 
    FROM (tt2                              +
858
 
    JOIN tt3 tt3(ax, b, c) USING (b, c)) j;
 
851
                pg_get_viewdef                
 
852
----------------------------------------------
 
853
  SELECT j.b,                                +
 
854
     j.c,                                    +
 
855
     j.a,                                    +
 
856
     j.ax                                    +
 
857
    FROM (tt2                                +
 
858
      JOIN tt3 tt3(ax, b, c) USING (b, c)) j;
859
859
(1 row)
860
860
 
861
861
select pg_get_viewdef('v2', true);
862
 
             pg_get_viewdef             
863
 
----------------------------------------
864
 
  SELECT tt2.b,                        +
865
 
     tt3.c,                            +
866
 
     tt2.a,                            +
867
 
     tt3.ax,                           +
868
 
     tt4.ay,                           +
869
 
     tt4.q                             +
870
 
    FROM tt2                           +
871
 
    JOIN tt3 tt3(ax, b, c) USING (b, c)+
872
 
    JOIN tt4 USING (b);
 
862
              pg_get_viewdef              
 
863
------------------------------------------
 
864
  SELECT tt2.b,                          +
 
865
     tt3.c,                              +
 
866
     tt2.a,                              +
 
867
     tt3.ax,                             +
 
868
     tt4.ay,                             +
 
869
     tt4.q                               +
 
870
    FROM tt2                             +
 
871
      JOIN tt3 tt3(ax, b, c) USING (b, c)+
 
872
      JOIN tt4 USING (b);
873
873
(1 row)
874
874
 
875
875
select pg_get_viewdef('v2a', true);
876
 
             pg_get_viewdef             
877
 
----------------------------------------
878
 
  SELECT j.b,                          +
879
 
     j.c,                              +
880
 
     j.a,                              +
881
 
     j.ax,                             +
882
 
     j.ay,                             +
883
 
     j.q                               +
884
 
    FROM (tt2                          +
885
 
    JOIN tt3 tt3(ax, b, c) USING (b, c)+
886
 
    JOIN tt4 USING (b)) j;
 
876
              pg_get_viewdef              
 
877
------------------------------------------
 
878
  SELECT j.b,                            +
 
879
     j.c,                                +
 
880
     j.a,                                +
 
881
     j.ax,                               +
 
882
     j.ay,                               +
 
883
     j.q                                 +
 
884
    FROM (tt2                            +
 
885
      JOIN tt3 tt3(ax, b, c) USING (b, c)+
 
886
      JOIN tt4 USING (b)) j;
887
887
(1 row)
888
888
 
889
889
select pg_get_viewdef('v3', true);
890
 
             pg_get_viewdef             
891
 
----------------------------------------
892
 
  SELECT b,                            +
893
 
     tt3.c,                            +
894
 
     tt2.a,                            +
895
 
     tt3.ax,                           +
896
 
     tt4.ay,                           +
897
 
     tt4.q                             +
898
 
    FROM tt2                           +
899
 
    JOIN tt3 tt3(ax, b, c) USING (b, c)+
900
 
    FULL JOIN tt4 USING (b);
 
890
              pg_get_viewdef              
 
891
------------------------------------------
 
892
  SELECT b,                              +
 
893
     tt3.c,                              +
 
894
     tt2.a,                              +
 
895
     tt3.ax,                             +
 
896
     tt4.ay,                             +
 
897
     tt4.q                               +
 
898
    FROM tt2                             +
 
899
      JOIN tt3 tt3(ax, b, c) USING (b, c)+
 
900
      FULL JOIN tt4 USING (b);
901
901
(1 row)
902
902
 
903
903
alter table tt3 add column c int;
904
904
alter table tt3 add column e int;
905
905
select pg_get_viewdef('v1', true);
906
 
                 pg_get_viewdef                  
907
 
-------------------------------------------------
908
 
  SELECT tt2.b,                                 +
909
 
     tt3.c,                                     +
910
 
     tt2.a,                                     +
911
 
     tt3.ax                                     +
912
 
    FROM tt2                                    +
913
 
    JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c);
 
906
                  pg_get_viewdef                   
 
907
---------------------------------------------------
 
908
  SELECT tt2.b,                                   +
 
909
     tt3.c,                                       +
 
910
     tt2.a,                                       +
 
911
     tt3.ax                                       +
 
912
    FROM tt2                                      +
 
913
      JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c);
914
914
(1 row)
915
915
 
916
916
select pg_get_viewdef('v1a', true);
917
 
                                 pg_get_viewdef                                  
918
 
---------------------------------------------------------------------------------
919
 
  SELECT j.b,                                                                   +
920
 
     j.c,                                                                       +
921
 
     j.a,                                                                       +
922
 
     j.ax                                                                       +
923
 
    FROM (tt2                                                                   +
924
 
    JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, d, e, ax, c_1, e_1);
 
917
                                  pg_get_viewdef                                   
 
918
-----------------------------------------------------------------------------------
 
919
  SELECT j.b,                                                                     +
 
920
     j.c,                                                                         +
 
921
     j.a,                                                                         +
 
922
     j.ax                                                                         +
 
923
    FROM (tt2                                                                     +
 
924
      JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, d, e, ax, c_1, e_1);
925
925
(1 row)
926
926
 
927
927
select pg_get_viewdef('v2', true);
928
 
                 pg_get_viewdef                 
929
 
------------------------------------------------
930
 
  SELECT tt2.b,                                +
931
 
     tt3.c,                                    +
932
 
     tt2.a,                                    +
933
 
     tt3.ax,                                   +
934
 
     tt4.ay,                                   +
935
 
     tt4.q                                     +
936
 
    FROM tt2                                   +
937
 
    JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
938
 
    JOIN tt4 USING (b);
 
928
                  pg_get_viewdef                  
 
929
--------------------------------------------------
 
930
  SELECT tt2.b,                                  +
 
931
     tt3.c,                                      +
 
932
     tt2.a,                                      +
 
933
     tt3.ax,                                     +
 
934
     tt4.ay,                                     +
 
935
     tt4.q                                       +
 
936
    FROM tt2                                     +
 
937
      JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
 
938
      JOIN tt4 USING (b);
939
939
(1 row)
940
940
 
941
941
select pg_get_viewdef('v2a', true);
942
 
                        pg_get_viewdef                         
943
 
---------------------------------------------------------------
944
 
  SELECT j.b,                                                 +
945
 
     j.c,                                                     +
946
 
     j.a,                                                     +
947
 
     j.ax,                                                    +
948
 
     j.ay,                                                    +
949
 
     j.q                                                      +
950
 
    FROM (tt2                                                 +
951
 
    JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)               +
952
 
    JOIN tt4 USING (b)) j(b, c, a, d, e, ax, c_1, e_1, ay, q);
 
942
                         pg_get_viewdef                          
 
943
-----------------------------------------------------------------
 
944
  SELECT j.b,                                                   +
 
945
     j.c,                                                       +
 
946
     j.a,                                                       +
 
947
     j.ax,                                                      +
 
948
     j.ay,                                                      +
 
949
     j.q                                                        +
 
950
    FROM (tt2                                                   +
 
951
      JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)               +
 
952
      JOIN tt4 USING (b)) j(b, c, a, d, e, ax, c_1, e_1, ay, q);
953
953
(1 row)
954
954
 
955
955
select pg_get_viewdef('v3', true);
956
 
                 pg_get_viewdef                 
957
 
------------------------------------------------
958
 
  SELECT b,                                    +
959
 
     tt3.c,                                    +
960
 
     tt2.a,                                    +
961
 
     tt3.ax,                                   +
962
 
     tt4.ay,                                   +
963
 
     tt4.q                                     +
964
 
    FROM tt2                                   +
965
 
    JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
966
 
    FULL JOIN tt4 USING (b);
 
956
                  pg_get_viewdef                  
 
957
--------------------------------------------------
 
958
  SELECT b,                                      +
 
959
     tt3.c,                                      +
 
960
     tt2.a,                                      +
 
961
     tt3.ax,                                     +
 
962
     tt4.ay,                                     +
 
963
     tt4.q                                       +
 
964
    FROM tt2                                     +
 
965
      JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
 
966
      FULL JOIN tt4 USING (b);
967
967
(1 row)
968
968
 
969
969
alter table tt2 drop column d;
970
970
select pg_get_viewdef('v1', true);
971
 
                 pg_get_viewdef                  
972
 
-------------------------------------------------
973
 
  SELECT tt2.b,                                 +
974
 
     tt3.c,                                     +
975
 
     tt2.a,                                     +
976
 
     tt3.ax                                     +
977
 
    FROM tt2                                    +
978
 
    JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c);
 
971
                  pg_get_viewdef                   
 
972
---------------------------------------------------
 
973
  SELECT tt2.b,                                   +
 
974
     tt3.c,                                       +
 
975
     tt2.a,                                       +
 
976
     tt3.ax                                       +
 
977
    FROM tt2                                      +
 
978
      JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c);
979
979
(1 row)
980
980
 
981
981
select pg_get_viewdef('v1a', true);
982
 
                                pg_get_viewdef                                
983
 
------------------------------------------------------------------------------
984
 
  SELECT j.b,                                                                +
985
 
     j.c,                                                                    +
986
 
     j.a,                                                                    +
987
 
     j.ax                                                                    +
988
 
    FROM (tt2                                                                +
989
 
    JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, e, ax, c_1, e_1);
 
982
                                 pg_get_viewdef                                 
 
983
--------------------------------------------------------------------------------
 
984
  SELECT j.b,                                                                  +
 
985
     j.c,                                                                      +
 
986
     j.a,                                                                      +
 
987
     j.ax                                                                      +
 
988
    FROM (tt2                                                                  +
 
989
      JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, e, ax, c_1, e_1);
990
990
(1 row)
991
991
 
992
992
select pg_get_viewdef('v2', true);
993
 
                 pg_get_viewdef                 
994
 
------------------------------------------------
995
 
  SELECT tt2.b,                                +
996
 
     tt3.c,                                    +
997
 
     tt2.a,                                    +
998
 
     tt3.ax,                                   +
999
 
     tt4.ay,                                   +
1000
 
     tt4.q                                     +
1001
 
    FROM tt2                                   +
1002
 
    JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
1003
 
    JOIN tt4 USING (b);
 
993
                  pg_get_viewdef                  
 
994
--------------------------------------------------
 
995
  SELECT tt2.b,                                  +
 
996
     tt3.c,                                      +
 
997
     tt2.a,                                      +
 
998
     tt3.ax,                                     +
 
999
     tt4.ay,                                     +
 
1000
     tt4.q                                       +
 
1001
    FROM tt2                                     +
 
1002
      JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
 
1003
      JOIN tt4 USING (b);
1004
1004
(1 row)
1005
1005
 
1006
1006
select pg_get_viewdef('v2a', true);
1007
 
                       pg_get_viewdef                       
1008
 
------------------------------------------------------------
1009
 
  SELECT j.b,                                              +
1010
 
     j.c,                                                  +
1011
 
     j.a,                                                  +
1012
 
     j.ax,                                                 +
1013
 
     j.ay,                                                 +
1014
 
     j.q                                                   +
1015
 
    FROM (tt2                                              +
1016
 
    JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)            +
1017
 
    JOIN tt4 USING (b)) j(b, c, a, e, ax, c_1, e_1, ay, q);
 
1007
                        pg_get_viewdef                        
 
1008
--------------------------------------------------------------
 
1009
  SELECT j.b,                                                +
 
1010
     j.c,                                                    +
 
1011
     j.a,                                                    +
 
1012
     j.ax,                                                   +
 
1013
     j.ay,                                                   +
 
1014
     j.q                                                     +
 
1015
    FROM (tt2                                                +
 
1016
      JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)            +
 
1017
      JOIN tt4 USING (b)) j(b, c, a, e, ax, c_1, e_1, ay, q);
1018
1018
(1 row)
1019
1019
 
1020
1020
select pg_get_viewdef('v3', true);
1021
 
                 pg_get_viewdef                 
1022
 
------------------------------------------------
1023
 
  SELECT b,                                    +
1024
 
     tt3.c,                                    +
1025
 
     tt2.a,                                    +
1026
 
     tt3.ax,                                   +
1027
 
     tt4.ay,                                   +
1028
 
     tt4.q                                     +
1029
 
    FROM tt2                                   +
1030
 
    JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
1031
 
    FULL JOIN tt4 USING (b);
 
1021
                  pg_get_viewdef                  
 
1022
--------------------------------------------------
 
1023
  SELECT b,                                      +
 
1024
     tt3.c,                                      +
 
1025
     tt2.a,                                      +
 
1026
     tt3.ax,                                     +
 
1027
     tt4.ay,                                     +
 
1028
     tt4.q                                       +
 
1029
    FROM tt2                                     +
 
1030
      JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
 
1031
      FULL JOIN tt4 USING (b);
1032
1032
(1 row)
1033
1033
 
1034
1034
create table tt5 (a int, b int);
1035
1035
create table tt6 (c int, d int);
1036
1036
create view vv1 as select * from (tt5 cross join tt6) j(aa,bb,cc,dd);
1037
1037
select pg_get_viewdef('vv1', true);
1038
 
            pg_get_viewdef            
1039
 
--------------------------------------
1040
 
  SELECT j.aa,                       +
1041
 
     j.bb,                           +
1042
 
     j.cc,                           +
1043
 
     j.dd                            +
1044
 
    FROM (tt5                        +
1045
 
   CROSS JOIN tt6) j(aa, bb, cc, dd);
1046
 
(1 row)
1047
 
 
1048
 
alter table tt5 add column c int;
1049
 
select pg_get_viewdef('vv1', true);
1050
1038
             pg_get_viewdef              
1051
1039
-----------------------------------------
1052
1040
  SELECT j.aa,                          +
1054
1042
     j.cc,                              +
1055
1043
     j.dd                               +
1056
1044
    FROM (tt5                           +
1057
 
   CROSS JOIN tt6) j(aa, bb, c, cc, dd);
 
1045
      CROSS JOIN tt6) j(aa, bb, cc, dd);
 
1046
(1 row)
 
1047
 
 
1048
alter table tt5 add column c int;
 
1049
select pg_get_viewdef('vv1', true);
 
1050
               pg_get_viewdef               
 
1051
--------------------------------------------
 
1052
  SELECT j.aa,                             +
 
1053
     j.bb,                                 +
 
1054
     j.cc,                                 +
 
1055
     j.dd                                  +
 
1056
    FROM (tt5                              +
 
1057
      CROSS JOIN tt6) j(aa, bb, c, cc, dd);
1058
1058
(1 row)
1059
1059
 
1060
1060
alter table tt5 add column cc int;
1061
1061
select pg_get_viewdef('vv1', true);
 
1062
                  pg_get_viewdef                  
 
1063
--------------------------------------------------
 
1064
  SELECT j.aa,                                   +
 
1065
     j.bb,                                       +
 
1066
     j.cc,                                       +
 
1067
     j.dd                                        +
 
1068
    FROM (tt5                                    +
 
1069
      CROSS JOIN tt6) j(aa, bb, c, cc_1, cc, dd);
 
1070
(1 row)
 
1071
 
 
1072
alter table tt5 drop column c;
 
1073
select pg_get_viewdef('vv1', true);
1062
1074
                pg_get_viewdef                 
1063
1075
-----------------------------------------------
1064
1076
  SELECT j.aa,                                +
1066
1078
     j.cc,                                    +
1067
1079
     j.dd                                     +
1068
1080
    FROM (tt5                                 +
1069
 
   CROSS JOIN tt6) j(aa, bb, c, cc_1, cc, dd);
1070
 
(1 row)
1071
 
 
1072
 
alter table tt5 drop column c;
1073
 
select pg_get_viewdef('vv1', true);
1074
 
               pg_get_viewdef               
1075
 
--------------------------------------------
1076
 
  SELECT j.aa,                             +
1077
 
     j.bb,                                 +
1078
 
     j.cc,                                 +
1079
 
     j.dd                                  +
1080
 
    FROM (tt5                              +
1081
 
   CROSS JOIN tt6) j(aa, bb, cc_1, cc, dd);
 
1081
      CROSS JOIN tt6) j(aa, bb, cc_1, cc, dd);
1082
1082
(1 row)
1083
1083
 
1084
1084
-- Unnamed FULL JOIN USING is lots of fun too
1090
1090
union all
1091
1091
select * from tt7 full join tt8 using (x), tt8 tt8x;
1092
1092
select pg_get_viewdef('vv2', true);
1093
 
                     pg_get_viewdef                     
1094
 
--------------------------------------------------------
1095
 
          SELECT v.a,                                  +
1096
 
             v.b,                                      +
1097
 
             v.c,                                      +
1098
 
             v.d,                                      +
1099
 
             v.e                                       +
1100
 
            FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e)+
1101
 
 UNION ALL                                             +
1102
 
          SELECT x AS a,                               +
1103
 
             tt7.y AS b,                               +
1104
 
             tt8.z AS c,                               +
1105
 
             tt8x.x_1 AS d,                            +
1106
 
             tt8x.z AS e                               +
1107
 
            FROM tt7                                   +
1108
 
       FULL JOIN tt8 USING (x),                        +
1109
 
        tt8 tt8x(x_1, z);
 
1093
                 pg_get_viewdef                 
 
1094
------------------------------------------------
 
1095
  SELECT v.a,                                  +
 
1096
     v.b,                                      +
 
1097
     v.c,                                      +
 
1098
     v.d,                                      +
 
1099
     v.e                                       +
 
1100
    FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e)+
 
1101
 UNION ALL                                     +
 
1102
  SELECT x AS a,                               +
 
1103
     tt7.y AS b,                               +
 
1104
     tt8.z AS c,                               +
 
1105
     tt8x.x_1 AS d,                            +
 
1106
     tt8x.z AS e                               +
 
1107
    FROM tt7                                   +
 
1108
      FULL JOIN tt8 USING (x),                 +
 
1109
     tt8 tt8x(x_1, z);
1110
1110
(1 row)
1111
1111
 
1112
1112
create view vv3 as
1116
1116
  tt7 full join tt8 using (x),
1117
1117
  tt7 tt7x full join tt8 tt8x using (x);
1118
1118
select pg_get_viewdef('vv3', true);
1119
 
                       pg_get_viewdef                        
1120
 
-------------------------------------------------------------
1121
 
          SELECT v.a,                                       +
1122
 
             v.b,                                           +
1123
 
             v.c,                                           +
1124
 
             v.x,                                           +
1125
 
             v.e,                                           +
1126
 
             v.f                                            +
1127
 
            FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f)+
1128
 
 UNION ALL                                                  +
1129
 
          SELECT x AS a,                                    +
1130
 
             tt7.y AS b,                                    +
1131
 
             tt8.z AS c,                                    +
1132
 
             x_1 AS x,                                      +
1133
 
             tt7x.y AS e,                                   +
1134
 
             tt8x.z AS f                                    +
1135
 
            FROM tt7                                        +
1136
 
       FULL JOIN tt8 USING (x),                             +
1137
 
     tt7 tt7x(x_1, y)                                       +
1138
 
    FULL JOIN tt8 tt8x(x_1, z) USING (x_1);
 
1119
                   pg_get_viewdef                    
 
1120
-----------------------------------------------------
 
1121
  SELECT v.a,                                       +
 
1122
     v.b,                                           +
 
1123
     v.c,                                           +
 
1124
     v.x,                                           +
 
1125
     v.e,                                           +
 
1126
     v.f                                            +
 
1127
    FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f)+
 
1128
 UNION ALL                                          +
 
1129
  SELECT x AS a,                                    +
 
1130
     tt7.y AS b,                                    +
 
1131
     tt8.z AS c,                                    +
 
1132
     x_1 AS x,                                      +
 
1133
     tt7x.y AS e,                                   +
 
1134
     tt8x.z AS f                                    +
 
1135
    FROM tt7                                        +
 
1136
      FULL JOIN tt8 USING (x),                      +
 
1137
     tt7 tt7x(x_1, y)                               +
 
1138
      FULL JOIN tt8 tt8x(x_1, z) USING (x_1);
1139
1139
(1 row)
1140
1140
 
1141
1141
create view vv4 as
1145
1145
  tt7 full join tt8 using (x),
1146
1146
  tt7 tt7x full join tt8 tt8x using (x) full join tt8 tt8y using (x);
1147
1147
select pg_get_viewdef('vv4', true);
1148
 
                          pg_get_viewdef                          
1149
 
------------------------------------------------------------------
1150
 
          SELECT v.a,                                            +
1151
 
             v.b,                                                +
1152
 
             v.c,                                                +
1153
 
             v.x,                                                +
1154
 
             v.e,                                                +
1155
 
             v.f,                                                +
1156
 
             v.g                                                 +
1157
 
            FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g)+
1158
 
 UNION ALL                                                       +
1159
 
          SELECT x AS a,                                         +
1160
 
             tt7.y AS b,                                         +
1161
 
             tt8.z AS c,                                         +
1162
 
             x_1 AS x,                                           +
1163
 
             tt7x.y AS e,                                        +
1164
 
             tt8x.z AS f,                                        +
1165
 
             tt8y.z AS g                                         +
1166
 
            FROM tt7                                             +
1167
 
       FULL JOIN tt8 USING (x),                                  +
1168
 
     tt7 tt7x(x_1, y)                                            +
1169
 
    FULL JOIN tt8 tt8x(x_1, z) USING (x_1)                       +
1170
 
    FULL JOIN tt8 tt8y(x_1, z) USING (x_1);
 
1148
                      pg_get_viewdef                      
 
1149
----------------------------------------------------------
 
1150
  SELECT v.a,                                            +
 
1151
     v.b,                                                +
 
1152
     v.c,                                                +
 
1153
     v.x,                                                +
 
1154
     v.e,                                                +
 
1155
     v.f,                                                +
 
1156
     v.g                                                 +
 
1157
    FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g)+
 
1158
 UNION ALL                                               +
 
1159
  SELECT x AS a,                                         +
 
1160
     tt7.y AS b,                                         +
 
1161
     tt8.z AS c,                                         +
 
1162
     x_1 AS x,                                           +
 
1163
     tt7x.y AS e,                                        +
 
1164
     tt8x.z AS f,                                        +
 
1165
     tt8y.z AS g                                         +
 
1166
    FROM tt7                                             +
 
1167
      FULL JOIN tt8 USING (x),                           +
 
1168
     tt7 tt7x(x_1, y)                                    +
 
1169
      FULL JOIN tt8 tt8x(x_1, z) USING (x_1)             +
 
1170
      FULL JOIN tt8 tt8y(x_1, z) USING (x_1);
1171
1171
(1 row)
1172
1172
 
1173
1173
alter table tt7 add column zz int;
1175
1175
alter table tt7 drop column zz;
1176
1176
alter table tt8 add column z2 int;
1177
1177
select pg_get_viewdef('vv2', true);
1178
 
                     pg_get_viewdef                     
1179
 
--------------------------------------------------------
1180
 
          SELECT v.a,                                  +
1181
 
             v.b,                                      +
1182
 
             v.c,                                      +
1183
 
             v.d,                                      +
1184
 
             v.e                                       +
1185
 
            FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e)+
1186
 
 UNION ALL                                             +
1187
 
          SELECT x AS a,                               +
1188
 
             tt7.y AS b,                               +
1189
 
             tt8.z AS c,                               +
1190
 
             tt8x.x_1 AS d,                            +
1191
 
             tt8x.z AS e                               +
1192
 
            FROM tt7                                   +
1193
 
       FULL JOIN tt8 USING (x),                        +
1194
 
        tt8 tt8x(x_1, z, z2);
 
1178
                 pg_get_viewdef                 
 
1179
------------------------------------------------
 
1180
  SELECT v.a,                                  +
 
1181
     v.b,                                      +
 
1182
     v.c,                                      +
 
1183
     v.d,                                      +
 
1184
     v.e                                       +
 
1185
    FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e)+
 
1186
 UNION ALL                                     +
 
1187
  SELECT x AS a,                               +
 
1188
     tt7.y AS b,                               +
 
1189
     tt8.z AS c,                               +
 
1190
     tt8x.x_1 AS d,                            +
 
1191
     tt8x.z AS e                               +
 
1192
    FROM tt7                                   +
 
1193
      FULL JOIN tt8 USING (x),                 +
 
1194
     tt8 tt8x(x_1, z, z2);
1195
1195
(1 row)
1196
1196
 
1197
1197
select pg_get_viewdef('vv3', true);
1198
 
                       pg_get_viewdef                        
1199
 
-------------------------------------------------------------
1200
 
          SELECT v.a,                                       +
1201
 
             v.b,                                           +
1202
 
             v.c,                                           +
1203
 
             v.x,                                           +
1204
 
             v.e,                                           +
1205
 
             v.f                                            +
1206
 
            FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f)+
1207
 
 UNION ALL                                                  +
1208
 
          SELECT x AS a,                                    +
1209
 
             tt7.y AS b,                                    +
1210
 
             tt8.z AS c,                                    +
1211
 
             x_1 AS x,                                      +
1212
 
             tt7x.y AS e,                                   +
1213
 
             tt8x.z AS f                                    +
1214
 
            FROM tt7                                        +
1215
 
       FULL JOIN tt8 USING (x),                             +
1216
 
     tt7 tt7x(x_1, y, z)                                    +
1217
 
    FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1);
 
1198
                   pg_get_viewdef                    
 
1199
-----------------------------------------------------
 
1200
  SELECT v.a,                                       +
 
1201
     v.b,                                           +
 
1202
     v.c,                                           +
 
1203
     v.x,                                           +
 
1204
     v.e,                                           +
 
1205
     v.f                                            +
 
1206
    FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f)+
 
1207
 UNION ALL                                          +
 
1208
  SELECT x AS a,                                    +
 
1209
     tt7.y AS b,                                    +
 
1210
     tt8.z AS c,                                    +
 
1211
     x_1 AS x,                                      +
 
1212
     tt7x.y AS e,                                   +
 
1213
     tt8x.z AS f                                    +
 
1214
    FROM tt7                                        +
 
1215
      FULL JOIN tt8 USING (x),                      +
 
1216
     tt7 tt7x(x_1, y, z)                            +
 
1217
      FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1);
1218
1218
(1 row)
1219
1219
 
1220
1220
select pg_get_viewdef('vv4', true);
1221
 
                          pg_get_viewdef                          
1222
 
------------------------------------------------------------------
1223
 
          SELECT v.a,                                            +
1224
 
             v.b,                                                +
1225
 
             v.c,                                                +
1226
 
             v.x,                                                +
1227
 
             v.e,                                                +
1228
 
             v.f,                                                +
1229
 
             v.g                                                 +
1230
 
            FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g)+
1231
 
 UNION ALL                                                       +
1232
 
          SELECT x AS a,                                         +
1233
 
             tt7.y AS b,                                         +
1234
 
             tt8.z AS c,                                         +
1235
 
             x_1 AS x,                                           +
1236
 
             tt7x.y AS e,                                        +
1237
 
             tt8x.z AS f,                                        +
1238
 
             tt8y.z AS g                                         +
1239
 
            FROM tt7                                             +
1240
 
       FULL JOIN tt8 USING (x),                                  +
1241
 
     tt7 tt7x(x_1, y, z)                                         +
1242
 
    FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1)                   +
1243
 
    FULL JOIN tt8 tt8y(x_1, z, z2) USING (x_1);
 
1221
                      pg_get_viewdef                      
 
1222
----------------------------------------------------------
 
1223
  SELECT v.a,                                            +
 
1224
     v.b,                                                +
 
1225
     v.c,                                                +
 
1226
     v.x,                                                +
 
1227
     v.e,                                                +
 
1228
     v.f,                                                +
 
1229
     v.g                                                 +
 
1230
    FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g)+
 
1231
 UNION ALL                                               +
 
1232
  SELECT x AS a,                                         +
 
1233
     tt7.y AS b,                                         +
 
1234
     tt8.z AS c,                                         +
 
1235
     x_1 AS x,                                           +
 
1236
     tt7x.y AS e,                                        +
 
1237
     tt8x.z AS f,                                        +
 
1238
     tt8y.z AS g                                         +
 
1239
    FROM tt7                                             +
 
1240
      FULL JOIN tt8 USING (x),                           +
 
1241
     tt7 tt7x(x_1, y, z)                                 +
 
1242
      FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1)         +
 
1243
      FULL JOIN tt8 tt8y(x_1, z, z2) USING (x_1);
1244
1244
(1 row)
1245
1245
 
1246
1246
-- Implicit coercions in a JOIN USING create issues similar to FULL JOIN
1252
1252
union all
1253
1253
select * from tt7a left join tt8a using (x), tt8a tt8ax;
1254
1254
select pg_get_viewdef('vv2a', true);
1255
 
                         pg_get_viewdef                         
1256
 
----------------------------------------------------------------
1257
 
          SELECT v.a,                                          +
1258
 
             v.b,                                              +
1259
 
             v.c,                                              +
1260
 
             v.d,                                              +
1261
 
             v.e                                               +
1262
 
            FROM ( VALUES (now(),2,3,now(),5)) v(a, b, c, d, e)+
1263
 
 UNION ALL                                                     +
1264
 
          SELECT x AS a,                                       +
1265
 
             tt7a.y AS b,                                      +
1266
 
             tt8a.z AS c,                                      +
1267
 
             tt8ax.x_1 AS d,                                   +
1268
 
             tt8ax.z AS e                                      +
1269
 
            FROM tt7a                                          +
1270
 
       LEFT JOIN tt8a USING (x),                               +
1271
 
        tt8a tt8ax(x_1, z);
 
1255
                     pg_get_viewdef                     
 
1256
--------------------------------------------------------
 
1257
  SELECT v.a,                                          +
 
1258
     v.b,                                              +
 
1259
     v.c,                                              +
 
1260
     v.d,                                              +
 
1261
     v.e                                               +
 
1262
    FROM ( VALUES (now(),2,3,now(),5)) v(a, b, c, d, e)+
 
1263
 UNION ALL                                             +
 
1264
  SELECT x AS a,                                       +
 
1265
     tt7a.y AS b,                                      +
 
1266
     tt8a.z AS c,                                      +
 
1267
     tt8ax.x_1 AS d,                                   +
 
1268
     tt8ax.z AS e                                      +
 
1269
    FROM tt7a                                          +
 
1270
      LEFT JOIN tt8a USING (x),                        +
 
1271
     tt8a tt8ax(x_1, z);
1272
1272
(1 row)
1273
1273
 
1274
1274
--
1278
1278
create table tt10 (x int, z int);
1279
1279
create view vv5 as select x,y,z from tt9 join tt10 using(x);
1280
1280
select pg_get_viewdef('vv5', true);
1281
 
     pg_get_viewdef      
1282
 
-------------------------
1283
 
  SELECT tt9.x,         +
1284
 
     tt9.y,             +
1285
 
     tt10.z             +
1286
 
    FROM tt9            +
1287
 
    JOIN tt10 USING (x);
 
1281
      pg_get_viewdef       
 
1282
---------------------------
 
1283
  SELECT tt9.x,           +
 
1284
     tt9.y,               +
 
1285
     tt10.z               +
 
1286
    FROM tt9              +
 
1287
      JOIN tt10 USING (x);
1288
1288
(1 row)
1289
1289
 
1290
1290
alter table tt9 drop column xx;
1291
1291
select pg_get_viewdef('vv5', true);
1292
 
     pg_get_viewdef      
1293
 
-------------------------
1294
 
  SELECT tt9.x,         +
1295
 
     tt9.y,             +
1296
 
     tt10.z             +
1297
 
    FROM tt9            +
1298
 
    JOIN tt10 USING (x);
 
1292
      pg_get_viewdef       
 
1293
---------------------------
 
1294
  SELECT tt9.x,           +
 
1295
     tt9.y,               +
 
1296
     tt10.z               +
 
1297
    FROM tt9              +
 
1298
      JOIN tt10 USING (x);
 
1299
(1 row)
 
1300
 
 
1301
--
 
1302
-- Another corner case is that we might add a column to a table below a
 
1303
-- JOIN USING, and thereby make the USING column name ambiguous
 
1304
--
 
1305
create table tt11 (x int, y int);
 
1306
create table tt12 (x int, z int);
 
1307
create table tt13 (z int, q int);
 
1308
create view vv6 as select x,y,z,q from
 
1309
  (tt11 join tt12 using(x)) join tt13 using(z);
 
1310
select pg_get_viewdef('vv6', true);
 
1311
      pg_get_viewdef       
 
1312
---------------------------
 
1313
  SELECT tt11.x,          +
 
1314
     tt11.y,              +
 
1315
     tt12.z,              +
 
1316
     tt13.q               +
 
1317
    FROM tt11             +
 
1318
      JOIN tt12 USING (x) +
 
1319
      JOIN tt13 USING (z);
 
1320
(1 row)
 
1321
 
 
1322
alter table tt11 add column z int;
 
1323
select pg_get_viewdef('vv6', true);
 
1324
        pg_get_viewdef        
 
1325
------------------------------
 
1326
  SELECT tt11.x,             +
 
1327
     tt11.y,                 +
 
1328
     tt12.z,                 +
 
1329
     tt13.q                  +
 
1330
    FROM tt11 tt11(x, y, z_1)+
 
1331
      JOIN tt12 USING (x)    +
 
1332
      JOIN tt13 USING (z);
 
1333
(1 row)
 
1334
 
 
1335
--
 
1336
-- Check some cases involving dropped columns in a function's rowtype result
 
1337
--
 
1338
create table tt14t (f1 text, f2 text, f3 text, f4 text);
 
1339
insert into tt14t values('foo', 'bar', 'baz', 'quux');
 
1340
alter table tt14t drop column f2;
 
1341
create function tt14f() returns setof tt14t as
 
1342
$$
 
1343
declare
 
1344
    rec1 record;
 
1345
begin
 
1346
    for rec1 in select * from tt14t
 
1347
    loop
 
1348
        return next rec1;
 
1349
    end loop;
 
1350
end;
 
1351
$$
 
1352
language plpgsql;
 
1353
create view tt14v as select t.* from tt14f() t;
 
1354
select pg_get_viewdef('tt14v', true);
 
1355
         pg_get_viewdef         
 
1356
--------------------------------
 
1357
  SELECT t.f1,                 +
 
1358
     t.f3,                     +
 
1359
     t.f4                      +
 
1360
    FROM tt14f() t(f1, f3, f4);
 
1361
(1 row)
 
1362
 
 
1363
select * from tt14v;
 
1364
 f1  | f3  |  f4  
 
1365
-----+-----+------
 
1366
 foo | baz | quux
 
1367
(1 row)
 
1368
 
 
1369
-- this perhaps should be rejected, but it isn't:
 
1370
alter table tt14t drop column f3;
 
1371
-- f3 is still in the view but will read as nulls
 
1372
select pg_get_viewdef('tt14v', true);
 
1373
         pg_get_viewdef         
 
1374
--------------------------------
 
1375
  SELECT t.f1,                 +
 
1376
     t.f3,                     +
 
1377
     t.f4                      +
 
1378
    FROM tt14f() t(f1, f3, f4);
 
1379
(1 row)
 
1380
 
 
1381
select * from tt14v;
 
1382
 f1  | f3 |  f4  
 
1383
-----+----+------
 
1384
 foo |    | quux
1299
1385
(1 row)
1300
1386
 
1301
1387
-- clean up all the random objects we made above