github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/external/pgjdbc (about) 1 # Reconstructed from: 2 # https://github.com/jordanlewis/pgjdbc/blob/462d505f01ec6180b30eaffabe51839dd126b90c/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java#L2391-L2408 3 4 # For testing, create the schema for the builtin tables. 5 exec-ddl 6 CREATE TABLE pg_type ( 7 oid OID NULL, 8 typname NAME NOT NULL, 9 typnamespace OID NULL, 10 typowner OID NULL, 11 typlen INT NULL, 12 typbyval BOOL NULL, 13 typtype STRING NULL, 14 typcategory STRING NULL, 15 typispreferred BOOL NULL, 16 typisdefined BOOL NULL, 17 typdelim STRING NULL, 18 typrelid OID NULL, 19 typelem OID NULL, 20 typarray OID NULL, 21 typinput OID NULL, 22 typoutput OID NULL, 23 typreceive OID NULL, 24 typsend OID NULL, 25 typmodin OID NULL, 26 typmodout OID NULL, 27 typanalyze OID NULL, 28 typalign STRING NULL, 29 typstorage STRING NULL, 30 typnotnull BOOL NULL, 31 typbasetype OID NULL, 32 typtypmod INT NULL, 33 typndims INT NULL, 34 typcollation OID NULL, 35 typdefaultbin STRING NULL, 36 typdefault STRING NULL, 37 typacl STRING[] NULL 38 ) 39 ---- 40 41 exec-ddl 42 CREATE TABLE pg_namespace ( 43 oid OID NULL, 44 nspname NAME NOT NULL, 45 nspowner OID NULL, 46 nspacl STRING[] NULL 47 ) 48 ---- 49 50 opt 51 SELECT 52 NULL AS type_cat, 53 n.nspname AS type_schem, 54 t.typname AS type_name, 55 NULL AS class_name, 56 CASE 57 WHEN t.typtype = 'c' THEN 'STRUCT' 58 ELSE 'DISTINCT' 59 END 60 AS data_type, 61 pg_catalog.obj_description(t.oid, 'pg_type') AS remarks, 62 CASE 63 WHEN t.typtype = 'd' 64 THEN ( 65 SELECT 66 CASE 67 WHEN typname = 'pgType' THEN 'sqlType' 68 ELSE 'OTHER' 69 END 70 FROM 71 pg_type 72 WHERE 73 oid = t.typbasetype 74 ) 75 ELSE NULL 76 END 77 AS base_type 78 FROM 79 pg_type AS t, pg_namespace AS n 80 WHERE 81 t.typnamespace = n.oid AND n.nspname != 'pg_catalog'; 82 ---- 83 project 84 ├── columns: type_cat:71 type_schem:34!null type_name:2!null class_name:71 data_type:72 remarks:73 base_type:74 85 ├── stable 86 ├── fd: ()-->(71) 87 ├── ensure-distinct-on 88 │ ├── columns: t.oid:1 t.typname:2!null t.typtype:7 nspname:34!null case:70 rownum:75!null 89 │ ├── grouping columns: rownum:75!null 90 │ ├── error: "more than one row returned by a subquery used as an expression" 91 │ ├── key: (75) 92 │ ├── fd: (75)-->(1,2,7,34,70) 93 │ ├── left-join (hash) 94 │ │ ├── columns: t.oid:1 t.typname:2!null t.typnamespace:3!null t.typtype:7 t.typbasetype:25 n.oid:33!null nspname:34!null pg_type.oid:38 case:70 rownum:75!null 95 │ │ ├── fd: (3)==(33), (33)==(3), (75)-->(1-3,7,25,33,34) 96 │ │ ├── ordinality 97 │ │ │ ├── columns: t.oid:1 t.typname:2!null t.typnamespace:3!null t.typtype:7 t.typbasetype:25 n.oid:33!null nspname:34!null rownum:75!null 98 │ │ │ ├── key: (75) 99 │ │ │ ├── fd: (3)==(33), (33)==(3), (75)-->(1-3,7,25,33,34) 100 │ │ │ └── inner-join (hash) 101 │ │ │ ├── columns: t.oid:1 t.typname:2!null t.typnamespace:3!null t.typtype:7 t.typbasetype:25 n.oid:33!null nspname:34!null 102 │ │ │ ├── fd: (3)==(33), (33)==(3) 103 │ │ │ ├── scan t 104 │ │ │ │ └── columns: t.oid:1 t.typname:2!null t.typnamespace:3 t.typtype:7 t.typbasetype:25 105 │ │ │ ├── select 106 │ │ │ │ ├── columns: n.oid:33 nspname:34!null 107 │ │ │ │ ├── scan n 108 │ │ │ │ │ └── columns: n.oid:33 nspname:34!null 109 │ │ │ │ └── filters 110 │ │ │ │ └── nspname:34 != 'pg_catalog' [outer=(34), constraints=(/34: (/NULL - /'pg_catalog') [/e'pg_catalog\x00' - ]; tight)] 111 │ │ │ └── filters 112 │ │ │ └── t.typnamespace:3 = n.oid:33 [outer=(3,33), constraints=(/3: (/NULL - ]; /33: (/NULL - ]), fd=(3)==(33), (33)==(3)] 113 │ │ ├── project 114 │ │ │ ├── columns: case:70!null pg_type.oid:38 115 │ │ │ ├── scan pg_type 116 │ │ │ │ └── columns: pg_type.oid:38 pg_type.typname:39!null 117 │ │ │ └── projections 118 │ │ │ └── CASE WHEN pg_type.typname:39 = 'pgType' THEN 'sqlType' ELSE 'OTHER' END [as=case:70, outer=(39)] 119 │ │ └── filters 120 │ │ └── pg_type.oid:38 = t.typbasetype:25 [outer=(25,38), constraints=(/25: (/NULL - ]; /38: (/NULL - ]), fd=(25)==(38), (38)==(25)] 121 │ └── aggregations 122 │ ├── const-agg [as=t.oid:1, outer=(1)] 123 │ │ └── t.oid:1 124 │ ├── const-agg [as=t.typname:2, outer=(2)] 125 │ │ └── t.typname:2 126 │ ├── const-agg [as=t.typtype:7, outer=(7)] 127 │ │ └── t.typtype:7 128 │ ├── const-agg [as=nspname:34, outer=(34)] 129 │ │ └── nspname:34 130 │ └── const-agg [as=case:70, outer=(70)] 131 │ └── case:70 132 └── projections 133 ├── NULL [as=type_cat:71] 134 ├── CASE WHEN t.typtype:7 = 'c' THEN 'STRUCT' ELSE 'DISTINCT' END [as=data_type:72, outer=(7)] 135 ├── obj_description(t.oid:1, 'pg_type') [as=remarks:73, outer=(1), stable] 136 └── CASE WHEN t.typtype:7 = 'd' THEN case:70 END [as=base_type:74, outer=(7,70)]