-
Notifications
You must be signed in to change notification settings - Fork 3.8k
/
udf_star
141 lines (116 loc) · 3.8 KB
/
udf_star
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
129
130
131
132
133
134
135
136
137
138
139
140
141
statement ok
CREATE TABLE t_onecol (a INT);
INSERT INTO t_onecol VALUES (1)
statement ok
CREATE TABLE t_twocol (a INT, b INT);
INSERT INTO t_twocol VALUES (1,2)
statement ok
CREATE FUNCTION f_unqualified_onecol() RETURNS INT AS
$$
SELECT * FROM t_onecol;
$$ LANGUAGE SQL;
statement ok
CREATE FUNCTION f_subquery() RETURNS INT AS
$$
SELECT * FROM (SELECT a FROM (SELECT * FROM t_onecol) AS foo) AS bar;
$$ LANGUAGE SQL;
statement ok
CREATE FUNCTION f_unqualified_twocol() RETURNS t_twocol AS
$$
SELECT * FROM t_twocol;
$$ LANGUAGE SQL;
statement ok
CREATE FUNCTION f_allcolsel() RETURNS t_twocol AS
$$
SELECT t_twocol.* FROM t_twocol;
$$ LANGUAGE SQL;
statement ok
CREATE FUNCTION f_allcolsel_alias() RETURNS t_twocol AS
$$
SELECT t1.* FROM t_twocol AS t1, t_twocol AS t2 WHERE t1.a = t2.a;
$$ LANGUAGE SQL;
statement ok
CREATE FUNCTION f_tuplestar() RETURNS t_twocol AS
$$
SELECT (t_twocol.*).* FROM t_twocol;
$$ LANGUAGE SQL;
statement ok
CREATE FUNCTION f_unqualified_multicol() RETURNS INT AS
$$
SELECT *, a FROM t_onecol;
SELECT a FROM t_onecol;
$$ LANGUAGE SQL;
statement ok
CREATE FUNCTION f_unqualified_doublestar() RETURNS INT AS
$$
SELECT *, * FROM t_onecol;
SELECT a FROM t_onecol;
$$ LANGUAGE SQL;
query TTT
SELECT oid, proname, prosrc
FROM pg_catalog.pg_proc WHERE proname LIKE 'f\_%' ORDER BY oid;
----
100108 f_unqualified_onecol SELECT t_onecol.a FROM test.public.t_onecol;
100109 f_subquery SELECT bar.a FROM (SELECT a FROM (SELECT t_onecol.a FROM test.public.t_onecol) AS foo) AS bar;
100110 f_unqualified_twocol SELECT t_twocol.a, t_twocol.b FROM test.public.t_twocol;
100111 f_allcolsel SELECT t_twocol.a, t_twocol.b FROM test.public.t_twocol;
100112 f_allcolsel_alias SELECT t1.a, t1.b FROM test.public.t_twocol AS t1, test.public.t_twocol AS t2 WHERE t1.a = t2.a;
100113 f_tuplestar SELECT t_twocol.a, t_twocol.b FROM test.public.t_twocol;
100114 f_unqualified_multicol SELECT t_onecol.a, a FROM test.public.t_onecol;
SELECT a FROM test.public.t_onecol;
100115 f_unqualified_doublestar SELECT t_onecol.a, t_onecol.a FROM test.public.t_onecol;
SELECT a FROM test.public.t_onecol;
query I
SELECT f_unqualified_onecol()
----
1
query I
SELECT f_subquery()
----
1
statement ok
ALTER TABLE t_onecol ADD COLUMN b INT DEFAULT 5;
query I
SELECT f_unqualified_onecol()
----
1
query I
SELECT f_subquery()
----
1
# It's ok to drop a column that was not used by the original UDF.
statement ok
ALTER TABLE t_onecol DROP COLUMN b;
query T
SELECT f_unqualified_twocol()
----
(1,2)
query T
SELECT f_allcolsel()
----
(1,2)
query T
SELECT f_allcolsel_alias()
----
(1,2)
statement ok
ALTER TABLE t_twocol ADD COLUMN c INT DEFAULT 5;
# TODO(#95558): With early binding, postgres returns an error after adding a
# column when the table is used as the return type. Note that this behavior is
# ok for late binding.
query T
SELECT f_unqualified_twocol()
----
(1,2)
# Altering a column type is not allowed in postgres or CRDB.
statement error pq: cannot alter type of column "b" because function "f_unqualified_twocol" depends on it
ALTER TABLE t_twocol ALTER b TYPE FLOAT;
# TODO(harding): Postgres allows column renaming when only referenced by UDFs.
statement error pq: cannot rename column "a" because function "f_unqualified_twocol" depends on it
ALTER TABLE t_twocol RENAME COLUMN a TO d;
# TODO(harding): Postgres allows table renaming when only referenced by UDFs.
statement error pq: cannot rename relation "t_twocol" because function "f_unqualified_twocol" depends on it
ALTER TABLE t_twocol RENAME TO t_twocol_prime;
# Dropping a column a UDF depends on is not allowed.
statement error pq: cannot drop column "b" because function "f_unqualified_twocol" depends on it
ALTER TABLE t_twocol DROP COLUMN b;