1#include "duckdb/catalog/default/default_functions.hpp"
2#include "duckdb/parser/parser.hpp"
3#include "duckdb/parser/parsed_data/create_macro_info.hpp"
4#include "duckdb/parser/expression/columnref_expression.hpp"
5#include "duckdb/catalog/catalog_entry/scalar_macro_catalog_entry.hpp"
6#include "duckdb/function/table_macro_function.hpp"
7
8#include "duckdb/function/scalar_macro_function.hpp"
9
10namespace duckdb {
11
12static DefaultMacro internal_macros[] = {
13 {DEFAULT_SCHEMA, .name: "current_role", .parameters: {nullptr}, .macro: "'duckdb'"}, // user name of current execution context
14 {DEFAULT_SCHEMA, .name: "current_user", .parameters: {nullptr}, .macro: "'duckdb'"}, // user name of current execution context
15 {DEFAULT_SCHEMA, .name: "current_catalog", .parameters: {nullptr}, .macro: "current_database()"}, // name of current database (called "catalog" in the SQL standard)
16 {DEFAULT_SCHEMA, .name: "user", .parameters: {nullptr}, .macro: "current_user"}, // equivalent to current_user
17 {DEFAULT_SCHEMA, .name: "session_user", .parameters: {nullptr}, .macro: "'duckdb'"}, // session user name
18 {.schema: "pg_catalog", .name: "inet_client_addr", .parameters: {nullptr}, .macro: "NULL"}, // address of the remote connection
19 {.schema: "pg_catalog", .name: "inet_client_port", .parameters: {nullptr}, .macro: "NULL"}, // port of the remote connection
20 {.schema: "pg_catalog", .name: "inet_server_addr", .parameters: {nullptr}, .macro: "NULL"}, // address of the local connection
21 {.schema: "pg_catalog", .name: "inet_server_port", .parameters: {nullptr}, .macro: "NULL"}, // port of the local connection
22 {.schema: "pg_catalog", .name: "pg_my_temp_schema", .parameters: {nullptr}, .macro: "0"}, // OID of session's temporary schema, or 0 if none
23 {.schema: "pg_catalog", .name: "pg_is_other_temp_schema", .parameters: {"schema_id", nullptr}, .macro: "false"}, // is schema another session's temporary schema?
24
25 {.schema: "pg_catalog", .name: "pg_conf_load_time", .parameters: {nullptr}, .macro: "current_timestamp"}, // configuration load time
26 {.schema: "pg_catalog", .name: "pg_postmaster_start_time", .parameters: {nullptr}, .macro: "current_timestamp"}, // server start time
27
28 {.schema: "pg_catalog", .name: "pg_typeof", .parameters: {"expression", nullptr}, .macro: "lower(typeof(expression))"}, // get the data type of any value
29
30 // privilege functions
31 // {"has_any_column_privilege", {"user", "table", "privilege", nullptr}, "true"}, //boolean //does user have privilege for any column of table
32 {.schema: "pg_catalog", .name: "has_any_column_privilege", .parameters: {"table", "privilege", nullptr}, .macro: "true"}, //boolean //does current user have privilege for any column of table
33 // {"has_column_privilege", {"user", "table", "column", "privilege", nullptr}, "true"}, //boolean //does user have privilege for column
34 {.schema: "pg_catalog", .name: "has_column_privilege", .parameters: {"table", "column", "privilege", nullptr}, .macro: "true"}, //boolean //does current user have privilege for column
35 // {"has_database_privilege", {"user", "database", "privilege", nullptr}, "true"}, //boolean //does user have privilege for database
36 {.schema: "pg_catalog", .name: "has_database_privilege", .parameters: {"database", "privilege", nullptr}, .macro: "true"}, //boolean //does current user have privilege for database
37 // {"has_foreign_data_wrapper_privilege", {"user", "fdw", "privilege", nullptr}, "true"}, //boolean //does user have privilege for foreign-data wrapper
38 {.schema: "pg_catalog", .name: "has_foreign_data_wrapper_privilege", .parameters: {"fdw", "privilege", nullptr}, .macro: "true"}, //boolean //does current user have privilege for foreign-data wrapper
39 // {"has_function_privilege", {"user", "function", "privilege", nullptr}, "true"}, //boolean //does user have privilege for function
40 {.schema: "pg_catalog", .name: "has_function_privilege", .parameters: {"function", "privilege", nullptr}, .macro: "true"}, //boolean //does current user have privilege for function
41 // {"has_language_privilege", {"user", "language", "privilege", nullptr}, "true"}, //boolean //does user have privilege for language
42 {.schema: "pg_catalog", .name: "has_language_privilege", .parameters: {"language", "privilege", nullptr}, .macro: "true"}, //boolean //does current user have privilege for language
43 // {"has_schema_privilege", {"user", "schema, privilege", nullptr}, "true"}, //boolean //does user have privilege for schema
44 {.schema: "pg_catalog", .name: "has_schema_privilege", .parameters: {"schema", "privilege", nullptr}, .macro: "true"}, //boolean //does current user have privilege for schema
45 // {"has_sequence_privilege", {"user", "sequence", "privilege", nullptr}, "true"}, //boolean //does user have privilege for sequence
46 {.schema: "pg_catalog", .name: "has_sequence_privilege", .parameters: {"sequence", "privilege", nullptr}, .macro: "true"}, //boolean //does current user have privilege for sequence
47 // {"has_server_privilege", {"user", "server", "privilege", nullptr}, "true"}, //boolean //does user have privilege for foreign server
48 {.schema: "pg_catalog", .name: "has_server_privilege", .parameters: {"server", "privilege", nullptr}, .macro: "true"}, //boolean //does current user have privilege for foreign server
49 // {"has_table_privilege", {"user", "table", "privilege", nullptr}, "true"}, //boolean //does user have privilege for table
50 {.schema: "pg_catalog", .name: "has_table_privilege", .parameters: {"table", "privilege", nullptr}, .macro: "true"}, //boolean //does current user have privilege for table
51 // {"has_tablespace_privilege", {"user", "tablespace", "privilege", nullptr}, "true"}, //boolean //does user have privilege for tablespace
52 {.schema: "pg_catalog", .name: "has_tablespace_privilege", .parameters: {"tablespace", "privilege", nullptr}, .macro: "true"}, //boolean //does current user have privilege for tablespace
53
54 // various postgres system functions
55 {.schema: "pg_catalog", .name: "pg_get_viewdef", .parameters: {"oid", nullptr}, .macro: "(select sql from duckdb_views() v where v.view_oid=oid)"},
56 {.schema: "pg_catalog", .name: "pg_get_constraintdef", .parameters: {"constraint_oid", "pretty_bool", nullptr}, .macro: "(select constraint_text from duckdb_constraints() d_constraint where d_constraint.table_oid=constraint_oid//1000000 and d_constraint.constraint_index=constraint_oid%1000000)"},
57 {.schema: "pg_catalog", .name: "pg_get_expr", .parameters: {"pg_node_tree", "relation_oid", nullptr}, .macro: "pg_node_tree"},
58 {.schema: "pg_catalog", .name: "format_pg_type", .parameters: {"type_name", nullptr}, .macro: "case when logical_type='FLOAT' then 'real' when logical_type='DOUBLE' then 'double precision' when logical_type='DECIMAL' then 'numeric' when logical_type='ENUM' then lower(type_name) when logical_type='VARCHAR' then 'character varying' when logical_type='BLOB' then 'bytea' when logical_type='TIMESTAMP' then 'timestamp without time zone' when logical_type='TIME' then 'time without time zone' else lower(logical_type) end"},
59 {.schema: "pg_catalog", .name: "format_type", .parameters: {"type_oid", "typemod", nullptr}, .macro: "(select format_pg_type(type_name) from duckdb_types() t where t.type_oid=type_oid) || case when typemod>0 then concat('(', typemod//1000, ',', typemod%1000, ')') else '' end"},
60
61 {.schema: "pg_catalog", .name: "pg_has_role", .parameters: {"user", "role", "privilege", nullptr}, .macro: "true"}, //boolean //does user have privilege for role
62 {.schema: "pg_catalog", .name: "pg_has_role", .parameters: {"role", "privilege", nullptr}, .macro: "true"}, //boolean //does current user have privilege for role
63
64 {.schema: "pg_catalog", .name: "col_description", .parameters: {"table_oid", "column_number", nullptr}, .macro: "NULL"}, // get comment for a table column
65 {.schema: "pg_catalog", .name: "obj_description", .parameters: {"object_oid", "catalog_name", nullptr}, .macro: "NULL"}, // get comment for a database object
66 {.schema: "pg_catalog", .name: "shobj_description", .parameters: {"object_oid", "catalog_name", nullptr}, .macro: "NULL"}, // get comment for a shared database object
67
68 // visibility functions
69 {.schema: "pg_catalog", .name: "pg_collation_is_visible", .parameters: {"collation_oid", nullptr}, .macro: "true"},
70 {.schema: "pg_catalog", .name: "pg_conversion_is_visible", .parameters: {"conversion_oid", nullptr}, .macro: "true"},
71 {.schema: "pg_catalog", .name: "pg_function_is_visible", .parameters: {"function_oid", nullptr}, .macro: "true"},
72 {.schema: "pg_catalog", .name: "pg_opclass_is_visible", .parameters: {"opclass_oid", nullptr}, .macro: "true"},
73 {.schema: "pg_catalog", .name: "pg_operator_is_visible", .parameters: {"operator_oid", nullptr}, .macro: "true"},
74 {.schema: "pg_catalog", .name: "pg_opfamily_is_visible", .parameters: {"opclass_oid", nullptr}, .macro: "true"},
75 {.schema: "pg_catalog", .name: "pg_table_is_visible", .parameters: {"table_oid", nullptr}, .macro: "true"},
76 {.schema: "pg_catalog", .name: "pg_ts_config_is_visible", .parameters: {"config_oid", nullptr}, .macro: "true"},
77 {.schema: "pg_catalog", .name: "pg_ts_dict_is_visible", .parameters: {"dict_oid", nullptr}, .macro: "true"},
78 {.schema: "pg_catalog", .name: "pg_ts_parser_is_visible", .parameters: {"parser_oid", nullptr}, .macro: "true"},
79 {.schema: "pg_catalog", .name: "pg_ts_template_is_visible", .parameters: {"template_oid", nullptr}, .macro: "true"},
80 {.schema: "pg_catalog", .name: "pg_type_is_visible", .parameters: {"type_oid", nullptr}, .macro: "true"},
81
82 {.schema: "pg_catalog", .name: "pg_size_pretty", .parameters: {"bytes", nullptr}, .macro: "format_bytes(bytes)"},
83
84 {DEFAULT_SCHEMA, .name: "round_even", .parameters: {"x", "n", nullptr}, .macro: "CASE ((abs(x) * power(10, n+1)) % 10) WHEN 5 THEN round(x/2, n) * 2 ELSE round(x, n) END"},
85 {DEFAULT_SCHEMA, .name: "roundbankers", .parameters: {"x", "n", nullptr}, .macro: "round_even(x, n)"},
86 {DEFAULT_SCHEMA, .name: "nullif", .parameters: {"a", "b", nullptr}, .macro: "CASE WHEN a=b THEN NULL ELSE a END"},
87 {DEFAULT_SCHEMA, .name: "list_append", .parameters: {"l", "e", nullptr}, .macro: "list_concat(l, list_value(e))"},
88 {DEFAULT_SCHEMA, .name: "array_append", .parameters: {"arr", "el", nullptr}, .macro: "list_append(arr, el)"},
89 {DEFAULT_SCHEMA, .name: "list_prepend", .parameters: {"e", "l", nullptr}, .macro: "list_concat(list_value(e), l)"},
90 {DEFAULT_SCHEMA, .name: "array_prepend", .parameters: {"el", "arr", nullptr}, .macro: "list_prepend(el, arr)"},
91 {DEFAULT_SCHEMA, .name: "array_pop_back", .parameters: {"arr", nullptr}, .macro: "arr[:LEN(arr)-1]"},
92 {DEFAULT_SCHEMA, .name: "array_pop_front", .parameters: {"arr", nullptr}, .macro: "arr[2:]"},
93 {DEFAULT_SCHEMA, .name: "array_push_back", .parameters: {"arr", "e", nullptr}, .macro: "list_concat(arr, list_value(e))"},
94 {DEFAULT_SCHEMA, .name: "array_push_front", .parameters: {"arr", "e", nullptr}, .macro: "list_concat(list_value(e), arr)"},
95 {DEFAULT_SCHEMA, .name: "array_to_string", .parameters: {"arr", "sep", nullptr}, .macro: "list_aggr(arr, 'string_agg', sep)"},
96 {DEFAULT_SCHEMA, .name: "generate_subscripts", .parameters: {"arr", "dim", nullptr}, .macro: "unnest(generate_series(1, array_length(arr, dim)))"},
97 {DEFAULT_SCHEMA, .name: "fdiv", .parameters: {"x", "y", nullptr}, .macro: "floor(x/y)"},
98 {DEFAULT_SCHEMA, .name: "fmod", .parameters: {"x", "y", nullptr}, .macro: "(x-y*floor(x/y))"},
99 {DEFAULT_SCHEMA, .name: "count_if", .parameters: {"l", nullptr}, .macro: "sum(if(l, 1, 0))"},
100 {DEFAULT_SCHEMA, .name: "split_part", .parameters: {"string", "delimiter", "position", nullptr}, .macro: "coalesce(string_split(string, delimiter)[position],'')"},
101
102 // algebraic list aggregates
103 {DEFAULT_SCHEMA, .name: "list_avg", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'avg')"},
104 {DEFAULT_SCHEMA, .name: "list_var_samp", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'var_samp')"},
105 {DEFAULT_SCHEMA, .name: "list_var_pop", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'var_pop')"},
106 {DEFAULT_SCHEMA, .name: "list_stddev_pop", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'stddev_pop')"},
107 {DEFAULT_SCHEMA, .name: "list_stddev_samp", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'stddev_samp')"},
108 {DEFAULT_SCHEMA, .name: "list_sem", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'sem')"},
109
110 // distributive list aggregates
111 {DEFAULT_SCHEMA, .name: "list_approx_count_distinct", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'approx_count_distinct')"},
112 {DEFAULT_SCHEMA, .name: "list_bit_xor", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'bit_xor')"},
113 {DEFAULT_SCHEMA, .name: "list_bit_or", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'bit_or')"},
114 {DEFAULT_SCHEMA, .name: "list_bit_and", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'bit_and')"},
115 {DEFAULT_SCHEMA, .name: "list_bool_and", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'bool_and')"},
116 {DEFAULT_SCHEMA, .name: "list_bool_or", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'bool_or')"},
117 {DEFAULT_SCHEMA, .name: "list_count", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'count')"},
118 {DEFAULT_SCHEMA, .name: "list_entropy", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'entropy')"},
119 {DEFAULT_SCHEMA, .name: "list_last", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'last')"},
120 {DEFAULT_SCHEMA, .name: "list_first", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'first')"},
121 {DEFAULT_SCHEMA, .name: "list_any_value", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'any_value')"},
122 {DEFAULT_SCHEMA, .name: "list_kurtosis", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'kurtosis')"},
123 {DEFAULT_SCHEMA, .name: "list_min", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'min')"},
124 {DEFAULT_SCHEMA, .name: "list_max", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'max')"},
125 {DEFAULT_SCHEMA, .name: "list_product", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'product')"},
126 {DEFAULT_SCHEMA, .name: "list_skewness", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'skewness')"},
127 {DEFAULT_SCHEMA, .name: "list_sum", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'sum')"},
128 {DEFAULT_SCHEMA, .name: "list_string_agg", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'string_agg')"},
129
130 // holistic list aggregates
131 {DEFAULT_SCHEMA, .name: "list_mode", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'mode')"},
132 {DEFAULT_SCHEMA, .name: "list_median", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'median')"},
133 {DEFAULT_SCHEMA, .name: "list_mad", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'mad')"},
134
135 // nested list aggregates
136 {DEFAULT_SCHEMA, .name: "list_histogram", .parameters: {"l", nullptr}, .macro: "list_aggr(l, 'histogram')"},
137
138 // date functions
139 {DEFAULT_SCHEMA, .name: "date_add", .parameters: {"date", "interval", nullptr}, .macro: "date + interval"},
140
141 {.schema: nullptr, .name: nullptr, .parameters: {nullptr}, .macro: nullptr}
142 };
143
144unique_ptr<CreateMacroInfo> DefaultFunctionGenerator::CreateInternalTableMacroInfo(DefaultMacro &default_macro, unique_ptr<MacroFunction> function) {
145 for (idx_t param_idx = 0; default_macro.parameters[param_idx] != nullptr; param_idx++) {
146 function->parameters.push_back(
147 x: make_uniq<ColumnRefExpression>(args&: default_macro.parameters[param_idx]));
148 }
149
150 auto bind_info = make_uniq<CreateMacroInfo>();
151 bind_info->schema = default_macro.schema;
152 bind_info->name = default_macro.name;
153 bind_info->temporary = true;
154 bind_info->internal = true;
155 bind_info->type = function->type == MacroType::TABLE_MACRO ? CatalogType::TABLE_MACRO_ENTRY : CatalogType::MACRO_ENTRY;
156 bind_info->function = std::move(function);
157 return bind_info;
158
159}
160
161unique_ptr<CreateMacroInfo> DefaultFunctionGenerator::CreateInternalMacroInfo(DefaultMacro &default_macro) {
162 // parse the expression
163 auto expressions = Parser::ParseExpressionList(select_list: default_macro.macro);
164 D_ASSERT(expressions.size() == 1);
165
166 auto result = make_uniq<ScalarMacroFunction>(args: std::move(expressions[0]));
167 return CreateInternalTableMacroInfo(default_macro, function: std::move(result));
168}
169
170unique_ptr<CreateMacroInfo> DefaultFunctionGenerator::CreateInternalTableMacroInfo(DefaultMacro &default_macro) {
171 Parser parser;
172 parser.ParseQuery(query: default_macro.macro);
173 D_ASSERT(parser.statements.size() == 1);
174 D_ASSERT(parser.statements[0]->type == StatementType::SELECT_STATEMENT);
175
176 auto &select = parser.statements[0]->Cast<SelectStatement>();
177 auto result = make_uniq<TableMacroFunction>(args: std::move(select.node));
178 return CreateInternalTableMacroInfo(default_macro, function: std::move(result));
179}
180
181static unique_ptr<CreateFunctionInfo> GetDefaultFunction(const string &input_schema, const string &input_name) {
182 auto schema = StringUtil::Lower(str: input_schema);
183 auto name = StringUtil::Lower(str: input_name);
184 for (idx_t index = 0; internal_macros[index].name != nullptr; index++) {
185 if (internal_macros[index].schema == schema && internal_macros[index].name == name) {
186 return DefaultFunctionGenerator::CreateInternalMacroInfo(default_macro&: internal_macros[index]);
187 }
188 }
189 return nullptr;
190}
191
192DefaultFunctionGenerator::DefaultFunctionGenerator(Catalog &catalog, SchemaCatalogEntry &schema)
193 : DefaultGenerator(catalog), schema(schema) {
194}
195
196unique_ptr<CatalogEntry> DefaultFunctionGenerator::CreateDefaultEntry(ClientContext &context,
197 const string &entry_name) {
198 auto info = GetDefaultFunction(input_schema: schema.name, input_name: entry_name);
199 if (info) {
200 return make_uniq_base<CatalogEntry, ScalarMacroCatalogEntry>(args&: catalog, args&: schema, args&: info->Cast<CreateMacroInfo>());
201 }
202 return nullptr;
203}
204
205vector<string> DefaultFunctionGenerator::GetDefaultEntries() {
206 vector<string> result;
207 for (idx_t index = 0; internal_macros[index].name != nullptr; index++) {
208 if (internal_macros[index].schema == schema.name) {
209 result.emplace_back(args&: internal_macros[index].name);
210 }
211 }
212 return result;
213}
214
215} // namespace duckdb
216