1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
|
# name: test/sql/catalog/function/test_table_macro.test
# description: Test SELECTMacro
# group: [function]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE test_tbl (id INT, name string);
statement ok
CREATE TABLE test2_tbl (id INT, name string);
statement ok
CREATE TABLE greek_tbl (id INT, name string);
statement ok
INSERT INTO test_tbl VALUES (1,'tom'), (2,'dick'),(3,'harry'), (4,'mary'), (5,'mungo'), (6,'midge');
statement ok
INSERT INTO test_tbl VALUES (20,'andrew'), (21,'boris'),(22,'Caleb'), (23,'david'), (24,'evan');
statement ok
INSERT INTO greek_tbl VALUES (1, 'alpha'), (2, 'beta'), (3, 'gamma'), (4, 'delta'), (5, 'epsilon'),(6, 'zeta'), (7, 'eta') , (8, 'theta'), (9, 'iota') , (10, 'kappa');
statement ok
CREATE MACRO xt(a,_name) as TABLE SELECT * FROM test_tbl WHERE(id>=a or name=_name);
statement ok
CREATE MACRO xt2(a,_name) as TABLE SELECT * FROM test_tbl WHERE(id>=a or name like _name);
statement ok
CREATE MACRO sgreek(a,b,c) as TABLE SELECT a,b FROM greek_tbl WHERE(id >= c);
query II
( SELECT* FROM xt(1, 'tom') UNION SELECT* FROM xt2(1, '%%%') ) INTERSECT SELECT* FROM xt(100,'midge');
----
6 midge
query II
(SELECT* FROM xt(1, 'tom') EXCEPT SELECT* FROM xt(20,'tom' )) INTERSECT SELECT* FROM xt(100,'harry');
----
3 harry
query II
SELECT * FROM xt(200,'andrew');
----
20 andrew
query II
SELECT * FROM xt2(100,'m%');
----
4 mary
5 mungo
6 midge
# check similar to
statement ok
CREATE MACRO xtm(cmp_str) as TABLE SELECT id, name FROM test_tbl WHERE( name similar to cmp_str);
statement ok
SELECT * FROM xtm('m.*');
# check regexp_matches
statement ok
CREATE MACRO xt_reg(cmp) as TABLE SELECT * FROM test_tbl WHERE regexp_matches(name ,cmp );
statement ok
SELECT * FROM xt_reg('^m');
# use regular macro for comparison
statement ok
CREATE MACRO cmp(a,m) as regexp_matches(a,m) or a similar to m;
statement ok
CREATE MACRO gm(m) as TABLE SELECT * FROM greek_tbl WHERE cmp(name,m);
statement ok
SELECT * FROM gm('^m');
# create a scalar macro with same name as table macro
statement ok
CREATE MACRO xt(a,b) as a+b;
# drop table macro
statement ok
DROP MACRO TABLE xt;
# use column identifer as a macro parameter
statement ok
CREATE MACRO xt(id, imax) as TABLE SELECT id,name FROM test_tbl WHERE id<=imax;
query II
SELECT * FROM xt(id,1);
----
1 tom
# try to create table macro with pre-existing table name "range"
statement ok
CREATE MACRO range(a,b) as TABLE select a,b from test_tbl;
# use table macro as a scalar macro
query II
SELECT * FROM test_tbl where id>=(SELECT max(id) FROM xt(id,30));
----
24 evan
# use table macro as a scalar macro
query II
SELECT * FROM greek_tbl where id<=(SELECT min(id) FROM xt(id,30));
----
1 alpha
# check that table macros are present in duckdb_functions() -
# nb they have the function_type 'table_macro'
query IIIIIIIII
SELECT schema_name, function_name, function_type, description, return_type, parameters, parameter_types, varargs, macro_definition FROM duckdb_functions() WHERE function_type = 'table_macro' AND
( function_name = 'sgreek' or function_name = 'xt') order by function_name;
----
main sgreek table_macro NULL NULL [a, b, c] [NULL, NULL, NULL] NULL SELECT a, b FROM greek_tbl WHERE (id >= c)
main xt table_macro NULL NULL [id, imax] [NULL, NULL] NULL SELECT id, "name" FROM test_tbl WHERE (id <= imax)
|
