1#include "duckdb/function/table/system_functions.hpp"
2
3#include "duckdb/catalog/catalog.hpp"
4#include "duckdb/catalog/catalog_entry/table_catalog_entry.hpp"
5#include "duckdb/catalog/catalog_entry/view_catalog_entry.hpp"
6#include "duckdb/common/exception.hpp"
7#include "duckdb/main/client_context.hpp"
8#include "duckdb/main/client_data.hpp"
9#include "duckdb/parser/constraints/not_null_constraint.hpp"
10
11#include <set>
12
13namespace duckdb {
14
15struct DuckDBColumnsData : public GlobalTableFunctionState {
16 DuckDBColumnsData() : offset(0), column_offset(0) {
17 }
18
19 vector<reference<CatalogEntry>> entries;
20 idx_t offset;
21 idx_t column_offset;
22};
23
24static unique_ptr<FunctionData> DuckDBColumnsBind(ClientContext &context, TableFunctionBindInput &input,
25 vector<LogicalType> &return_types, vector<string> &names) {
26 names.emplace_back(args: "database_name");
27 return_types.emplace_back(args: LogicalType::VARCHAR);
28
29 names.emplace_back(args: "database_oid");
30 return_types.emplace_back(args: LogicalType::BIGINT);
31
32 names.emplace_back(args: "schema_name");
33 return_types.emplace_back(args: LogicalType::VARCHAR);
34
35 names.emplace_back(args: "schema_oid");
36 return_types.emplace_back(args: LogicalType::BIGINT);
37
38 names.emplace_back(args: "table_name");
39 return_types.emplace_back(args: LogicalType::VARCHAR);
40
41 names.emplace_back(args: "table_oid");
42 return_types.emplace_back(args: LogicalType::BIGINT);
43
44 names.emplace_back(args: "column_name");
45 return_types.emplace_back(args: LogicalType::VARCHAR);
46
47 names.emplace_back(args: "column_index");
48 return_types.emplace_back(args: LogicalType::INTEGER);
49
50 names.emplace_back(args: "internal");
51 return_types.emplace_back(args: LogicalType::BOOLEAN);
52
53 names.emplace_back(args: "column_default");
54 return_types.emplace_back(args: LogicalType::VARCHAR);
55
56 names.emplace_back(args: "is_nullable");
57 return_types.emplace_back(args: LogicalType::BOOLEAN);
58
59 names.emplace_back(args: "data_type");
60 return_types.emplace_back(args: LogicalType::VARCHAR);
61
62 names.emplace_back(args: "data_type_id");
63 return_types.emplace_back(args: LogicalType::BIGINT);
64
65 names.emplace_back(args: "character_maximum_length");
66 return_types.emplace_back(args: LogicalType::INTEGER);
67
68 names.emplace_back(args: "numeric_precision");
69 return_types.emplace_back(args: LogicalType::INTEGER);
70
71 names.emplace_back(args: "numeric_precision_radix");
72 return_types.emplace_back(args: LogicalType::INTEGER);
73
74 names.emplace_back(args: "numeric_scale");
75 return_types.emplace_back(args: LogicalType::INTEGER);
76
77 return nullptr;
78}
79
80unique_ptr<GlobalTableFunctionState> DuckDBColumnsInit(ClientContext &context, TableFunctionInitInput &input) {
81 auto result = make_uniq<DuckDBColumnsData>();
82
83 // scan all the schemas for tables and views and collect them
84 auto schemas = Catalog::GetAllSchemas(context);
85 for (auto &schema : schemas) {
86 schema.get().Scan(context, type: CatalogType::TABLE_ENTRY,
87 callback: [&](CatalogEntry &entry) { result->entries.push_back(x: entry); });
88 }
89 return std::move(result);
90}
91
92class ColumnHelper {
93public:
94 static unique_ptr<ColumnHelper> Create(CatalogEntry &entry);
95
96 virtual ~ColumnHelper() {
97 }
98
99 virtual StandardEntry &Entry() = 0;
100 virtual idx_t NumColumns() = 0;
101 virtual const string &ColumnName(idx_t col) = 0;
102 virtual const LogicalType &ColumnType(idx_t col) = 0;
103 virtual const Value ColumnDefault(idx_t col) = 0;
104 virtual bool IsNullable(idx_t col) = 0;
105
106 void WriteColumns(idx_t index, idx_t start_col, idx_t end_col, DataChunk &output);
107};
108
109class TableColumnHelper : public ColumnHelper {
110public:
111 explicit TableColumnHelper(TableCatalogEntry &entry) : entry(entry) {
112 for (auto &constraint : entry.GetConstraints()) {
113 if (constraint->type == ConstraintType::NOT_NULL) {
114 auto &not_null = *reinterpret_cast<NotNullConstraint *>(constraint.get());
115 not_null_cols.insert(x: not_null.index.index);
116 }
117 }
118 }
119
120 StandardEntry &Entry() override {
121 return entry;
122 }
123 idx_t NumColumns() override {
124 return entry.GetColumns().LogicalColumnCount();
125 }
126 const string &ColumnName(idx_t col) override {
127 return entry.GetColumn(idx: LogicalIndex(col)).Name();
128 }
129 const LogicalType &ColumnType(idx_t col) override {
130 return entry.GetColumn(idx: LogicalIndex(col)).Type();
131 }
132 const Value ColumnDefault(idx_t col) override {
133 auto &column = entry.GetColumn(idx: LogicalIndex(col));
134 if (column.DefaultValue()) {
135 return Value(column.DefaultValue()->ToString());
136 }
137 return Value();
138 }
139 bool IsNullable(idx_t col) override {
140 return not_null_cols.find(x: col) == not_null_cols.end();
141 }
142
143private:
144 TableCatalogEntry &entry;
145 std::set<idx_t> not_null_cols;
146};
147
148class ViewColumnHelper : public ColumnHelper {
149public:
150 explicit ViewColumnHelper(ViewCatalogEntry &entry) : entry(entry) {
151 }
152
153 StandardEntry &Entry() override {
154 return entry;
155 }
156 idx_t NumColumns() override {
157 return entry.types.size();
158 }
159 const string &ColumnName(idx_t col) override {
160 return entry.aliases[col];
161 }
162 const LogicalType &ColumnType(idx_t col) override {
163 return entry.types[col];
164 }
165 const Value ColumnDefault(idx_t col) override {
166 return Value();
167 }
168 bool IsNullable(idx_t col) override {
169 return true;
170 }
171
172private:
173 ViewCatalogEntry &entry;
174};
175
176unique_ptr<ColumnHelper> ColumnHelper::Create(CatalogEntry &entry) {
177 switch (entry.type) {
178 case CatalogType::TABLE_ENTRY:
179 return make_uniq<TableColumnHelper>(args&: entry.Cast<TableCatalogEntry>());
180 case CatalogType::VIEW_ENTRY:
181 return make_uniq<ViewColumnHelper>(args&: entry.Cast<ViewCatalogEntry>());
182 default:
183 throw NotImplementedException("Unsupported catalog type for duckdb_columns");
184 }
185}
186
187void ColumnHelper::WriteColumns(idx_t start_index, idx_t start_col, idx_t end_col, DataChunk &output) {
188 for (idx_t i = start_col; i < end_col; i++) {
189 auto index = start_index + (i - start_col);
190 auto &entry = Entry();
191
192 idx_t col = 0;
193 // database_name, VARCHAR
194 output.SetValue(col_idx: col++, index, val: entry.catalog.GetName());
195 // database_oid, BIGINT
196 output.SetValue(col_idx: col++, index, val: Value::BIGINT(value: entry.catalog.GetOid()));
197 // schema_name, VARCHAR
198 output.SetValue(col_idx: col++, index, val: entry.schema.name);
199 // schema_oid, BIGINT
200 output.SetValue(col_idx: col++, index, val: Value::BIGINT(value: entry.schema.oid));
201 // table_name, VARCHAR
202 output.SetValue(col_idx: col++, index, val: entry.name);
203 // table_oid, BIGINT
204 output.SetValue(col_idx: col++, index, val: Value::BIGINT(value: entry.oid));
205 // column_name, VARCHAR
206 output.SetValue(col_idx: col++, index, val: Value(ColumnName(col: i)));
207 // column_index, INTEGER
208 output.SetValue(col_idx: col++, index, val: Value::INTEGER(value: i + 1));
209 // internal, BOOLEAN
210 output.SetValue(col_idx: col++, index, val: Value::BOOLEAN(value: entry.internal));
211 // column_default, VARCHAR
212 output.SetValue(col_idx: col++, index, val: Value(ColumnDefault(col: i)));
213 // is_nullable, BOOLEAN
214 output.SetValue(col_idx: col++, index, val: Value::BOOLEAN(value: IsNullable(col: i)));
215 // data_type, VARCHAR
216 const LogicalType &type = ColumnType(col: i);
217 output.SetValue(col_idx: col++, index, val: Value(type.ToString()));
218 // data_type_id, BIGINT
219 output.SetValue(col_idx: col++, index, val: Value::BIGINT(value: int(type.id())));
220 if (type == LogicalType::VARCHAR) {
221 // FIXME: need check constraints in place to set this correctly
222 // character_maximum_length, INTEGER
223 output.SetValue(col_idx: col++, index, val: Value());
224 } else {
225 // "character_maximum_length", PhysicalType::INTEGER
226 output.SetValue(col_idx: col++, index, val: Value());
227 }
228
229 Value numeric_precision, numeric_scale, numeric_precision_radix;
230 switch (type.id()) {
231 case LogicalTypeId::DECIMAL:
232 numeric_precision = Value::INTEGER(value: DecimalType::GetWidth(type));
233 numeric_scale = Value::INTEGER(value: DecimalType::GetScale(type));
234 numeric_precision_radix = Value::INTEGER(value: 10);
235 break;
236 case LogicalTypeId::HUGEINT:
237 numeric_precision = Value::INTEGER(value: 128);
238 numeric_scale = Value::INTEGER(value: 0);
239 numeric_precision_radix = Value::INTEGER(value: 2);
240 break;
241 case LogicalTypeId::BIGINT:
242 numeric_precision = Value::INTEGER(value: 64);
243 numeric_scale = Value::INTEGER(value: 0);
244 numeric_precision_radix = Value::INTEGER(value: 2);
245 break;
246 case LogicalTypeId::INTEGER:
247 numeric_precision = Value::INTEGER(value: 32);
248 numeric_scale = Value::INTEGER(value: 0);
249 numeric_precision_radix = Value::INTEGER(value: 2);
250 break;
251 case LogicalTypeId::SMALLINT:
252 numeric_precision = Value::INTEGER(value: 16);
253 numeric_scale = Value::INTEGER(value: 0);
254 numeric_precision_radix = Value::INTEGER(value: 2);
255 break;
256 case LogicalTypeId::TINYINT:
257 numeric_precision = Value::INTEGER(value: 8);
258 numeric_scale = Value::INTEGER(value: 0);
259 numeric_precision_radix = Value::INTEGER(value: 2);
260 break;
261 case LogicalTypeId::FLOAT:
262 numeric_precision = Value::INTEGER(value: 24);
263 numeric_scale = Value::INTEGER(value: 0);
264 numeric_precision_radix = Value::INTEGER(value: 2);
265 break;
266 case LogicalTypeId::DOUBLE:
267 numeric_precision = Value::INTEGER(value: 53);
268 numeric_scale = Value::INTEGER(value: 0);
269 numeric_precision_radix = Value::INTEGER(value: 2);
270 break;
271 default:
272 numeric_precision = Value();
273 numeric_scale = Value();
274 numeric_precision_radix = Value();
275 break;
276 }
277
278 // numeric_precision, INTEGER
279 output.SetValue(col_idx: col++, index, val: numeric_precision);
280 // numeric_precision_radix, INTEGER
281 output.SetValue(col_idx: col++, index, val: numeric_precision_radix);
282 // numeric_scale, INTEGER
283 output.SetValue(col_idx: col++, index, val: numeric_scale);
284 }
285}
286
287void DuckDBColumnsFunction(ClientContext &context, TableFunctionInput &data_p, DataChunk &output) {
288 auto &data = data_p.global_state->Cast<DuckDBColumnsData>();
289 if (data.offset >= data.entries.size()) {
290 // finished returning values
291 return;
292 }
293
294 // We need to track the offset of the relation we're writing as well as the last column
295 // we wrote from that relation (if any); it's possible that we can fill up the output
296 // with a partial list of columns from a relation and will need to pick up processing the
297 // next chunk at the same spot.
298 idx_t next = data.offset;
299 idx_t column_offset = data.column_offset;
300 idx_t index = 0;
301 while (next < data.entries.size() && index < STANDARD_VECTOR_SIZE) {
302 auto column_helper = ColumnHelper::Create(entry&: data.entries[next].get());
303 idx_t columns = column_helper->NumColumns();
304
305 // Check to see if we are going to exceed the maximum index for a DataChunk
306 if (index + (columns - column_offset) > STANDARD_VECTOR_SIZE) {
307 idx_t column_limit = column_offset + (STANDARD_VECTOR_SIZE - index);
308 output.SetCardinality(STANDARD_VECTOR_SIZE);
309 column_helper->WriteColumns(start_index: index, start_col: column_offset, end_col: column_limit, output);
310
311 // Make the current column limit the column offset when we process the next chunk
312 column_offset = column_limit;
313 break;
314 } else {
315 // Otherwise, write all of the columns from the current relation and
316 // then move on to the next one.
317 output.SetCardinality(index + (columns - column_offset));
318 column_helper->WriteColumns(start_index: index, start_col: column_offset, end_col: columns, output);
319 index += columns - column_offset;
320 next++;
321 column_offset = 0;
322 }
323 }
324 data.offset = next;
325 data.column_offset = column_offset;
326}
327
328void DuckDBColumnsFun::RegisterFunction(BuiltinFunctions &set) {
329 set.AddFunction(function: TableFunction("duckdb_columns", {}, DuckDBColumnsFunction, DuckDBColumnsBind, DuckDBColumnsInit));
330}
331
332} // namespace duckdb
333