Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit95712b1

Browse files
committed
Add missing regression files for SRF.
1 parentd84fe82 commit95712b1

File tree

2 files changed

+529
-0
lines changed

2 files changed

+529
-0
lines changed
Lines changed: 348 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,348 @@
1+
CREATE TABLE foo2(fooid int, f2 int);
2+
INSERT INTO foo2 VALUES(1, 11);
3+
INSERT INTO foo2 VALUES(2, 22);
4+
INSERT INTO foo2 VALUES(1, 111);
5+
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
6+
-- supposed to fail with ERROR
7+
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
8+
NOTICE: Adding missing FROM-clause entry for table "foo2"
9+
ERROR: FROM function expression may not refer to other relations of same query level
10+
-- function in subselect
11+
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
12+
fooid | f2
13+
-------+-----
14+
1 | 11
15+
1 | 111
16+
2 | 22
17+
(3 rows)
18+
19+
-- function in subselect
20+
select * from foo2 where f2 in (select f2 from foot(1) z where z.fooid = foo2.fooid) ORDER BY 1,2;
21+
fooid | f2
22+
-------+-----
23+
1 | 11
24+
1 | 111
25+
(2 rows)
26+
27+
-- function in subselect
28+
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = 1) ORDER BY 1,2;
29+
fooid | f2
30+
-------+-----
31+
1 | 11
32+
1 | 111
33+
(2 rows)
34+
35+
-- nested functions
36+
select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2;
37+
fooid | f2
38+
-------+-----
39+
1 | 11
40+
1 | 111
41+
(2 rows)
42+
43+
CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
44+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
45+
INSERT INTO foo VALUES(1,1,'Joe');
46+
INSERT INTO foo VALUES(1,2,'Ed');
47+
INSERT INTO foo VALUES(2,1,'Mary');
48+
-- sql, proretset = f, prorettype = b
49+
CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
50+
SELECT * FROM getfoo(1) AS t1;
51+
getfoo
52+
--------
53+
1
54+
(1 row)
55+
56+
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
57+
SELECT * FROM vw_getfoo;
58+
getfoo
59+
--------
60+
1
61+
(1 row)
62+
63+
-- sql, proretset = t, prorettype = b
64+
DROP FUNCTION getfoo(int);
65+
CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
66+
SELECT * FROM getfoo(1) AS t1;
67+
getfoo
68+
--------
69+
1
70+
1
71+
(2 rows)
72+
73+
DROP VIEW vw_getfoo;
74+
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
75+
SELECT * FROM vw_getfoo;
76+
getfoo
77+
--------
78+
1
79+
1
80+
(2 rows)
81+
82+
-- sql, proretset = t, prorettype = b
83+
DROP FUNCTION getfoo(int);
84+
CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
85+
SELECT * FROM getfoo(1) AS t1;
86+
getfoo
87+
--------
88+
Joe
89+
Ed
90+
(2 rows)
91+
92+
DROP VIEW vw_getfoo;
93+
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
94+
SELECT * FROM vw_getfoo;
95+
getfoo
96+
--------
97+
Joe
98+
Ed
99+
(2 rows)
100+
101+
-- sql, proretset = f, prorettype = c
102+
DROP FUNCTION getfoo(int);
103+
CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
104+
SELECT * FROM getfoo(1) AS t1;
105+
fooid | foosubid | fooname
106+
-------+----------+---------
107+
1 | 1 | Joe
108+
(1 row)
109+
110+
DROP VIEW vw_getfoo;
111+
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
112+
SELECT * FROM vw_getfoo;
113+
fooid | foosubid | fooname
114+
-------+----------+---------
115+
1 | 1 | Joe
116+
(1 row)
117+
118+
-- sql, proretset = t, prorettype = c
119+
DROP FUNCTION getfoo(int);
120+
CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
121+
SELECT * FROM getfoo(1) AS t1;
122+
fooid | foosubid | fooname
123+
-------+----------+---------
124+
1 | 1 | Joe
125+
1 | 2 | Ed
126+
(2 rows)
127+
128+
DROP VIEW vw_getfoo;
129+
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
130+
SELECT * FROM vw_getfoo;
131+
fooid | foosubid | fooname
132+
-------+----------+---------
133+
1 | 1 | Joe
134+
1 | 2 | Ed
135+
(2 rows)
136+
137+
-- plpgsql, proretset = f, prorettype = b
138+
DROP FUNCTION getfoo(int);
139+
CREATE FUNCTION getfoo(int) RETURNS int AS 'DECLARE fooint int; BEGIN SELECT fooid into fooint FROM foo WHERE fooid = $1; RETURN fooint; END;' LANGUAGE 'plpgsql';
140+
SELECT * FROM getfoo(1) AS t1;
141+
getfoo
142+
--------
143+
1
144+
(1 row)
145+
146+
DROP VIEW vw_getfoo;
147+
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
148+
SELECT * FROM vw_getfoo;
149+
getfoo
150+
--------
151+
1
152+
(1 row)
153+
154+
-- plpgsql, proretset = f, prorettype = c
155+
DROP FUNCTION getfoo(int);
156+
CREATE FUNCTION getfoo(int) RETURNS foo AS 'DECLARE footup foo%ROWTYPE; BEGIN SELECT * into footup FROM foo WHERE fooid = $1; RETURN footup; END;' LANGUAGE 'plpgsql';
157+
SELECT * FROM getfoo(1) AS t1;
158+
fooid | foosubid | fooname
159+
-------+----------+---------
160+
1 | 1 | Joe
161+
(1 row)
162+
163+
DROP VIEW vw_getfoo;
164+
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
165+
SELECT * FROM vw_getfoo;
166+
fooid | foosubid | fooname
167+
-------+----------+---------
168+
1 | 1 | Joe
169+
(1 row)
170+
171+
DROP TABLE foo2;
172+
DROP FUNCTION foot(int);
173+
DROP TABLE foo;
174+
DROP FUNCTION getfoo(int);
175+
DROP VIEW vw_getfoo;
176+
-- Rescan tests --
177+
CREATE TABLE foorescan (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
178+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'foorescan_pkey' for table 'foorescan'
179+
INSERT INTO foorescan values(5000,1,'abc.5000.1');
180+
INSERT INTO foorescan values(5001,1,'abc.5001.1');
181+
INSERT INTO foorescan values(5002,1,'abc.5002.1');
182+
INSERT INTO foorescan values(5003,1,'abc.5003.1');
183+
INSERT INTO foorescan values(5004,1,'abc.5004.1');
184+
INSERT INTO foorescan values(5005,1,'abc.5005.1');
185+
INSERT INTO foorescan values(5006,1,'abc.5006.1');
186+
INSERT INTO foorescan values(5007,1,'abc.5007.1');
187+
INSERT INTO foorescan values(5008,1,'abc.5008.1');
188+
INSERT INTO foorescan values(5009,1,'abc.5009.1');
189+
INSERT INTO foorescan values(5000,2,'abc.5000.2');
190+
INSERT INTO foorescan values(5001,2,'abc.5001.2');
191+
INSERT INTO foorescan values(5002,2,'abc.5002.2');
192+
INSERT INTO foorescan values(5003,2,'abc.5003.2');
193+
INSERT INTO foorescan values(5004,2,'abc.5004.2');
194+
INSERT INTO foorescan values(5005,2,'abc.5005.2');
195+
INSERT INTO foorescan values(5006,2,'abc.5006.2');
196+
INSERT INTO foorescan values(5007,2,'abc.5007.2');
197+
INSERT INTO foorescan values(5008,2,'abc.5008.2');
198+
INSERT INTO foorescan values(5009,2,'abc.5009.2');
199+
INSERT INTO foorescan values(5000,3,'abc.5000.3');
200+
INSERT INTO foorescan values(5001,3,'abc.5001.3');
201+
INSERT INTO foorescan values(5002,3,'abc.5002.3');
202+
INSERT INTO foorescan values(5003,3,'abc.5003.3');
203+
INSERT INTO foorescan values(5004,3,'abc.5004.3');
204+
INSERT INTO foorescan values(5005,3,'abc.5005.3');
205+
INSERT INTO foorescan values(5006,3,'abc.5006.3');
206+
INSERT INTO foorescan values(5007,3,'abc.5007.3');
207+
INSERT INTO foorescan values(5008,3,'abc.5008.3');
208+
INSERT INTO foorescan values(5009,3,'abc.5009.3');
209+
INSERT INTO foorescan values(5000,4,'abc.5000.4');
210+
INSERT INTO foorescan values(5001,4,'abc.5001.4');
211+
INSERT INTO foorescan values(5002,4,'abc.5002.4');
212+
INSERT INTO foorescan values(5003,4,'abc.5003.4');
213+
INSERT INTO foorescan values(5004,4,'abc.5004.4');
214+
INSERT INTO foorescan values(5005,4,'abc.5005.4');
215+
INSERT INTO foorescan values(5006,4,'abc.5006.4');
216+
INSERT INTO foorescan values(5007,4,'abc.5007.4');
217+
INSERT INTO foorescan values(5008,4,'abc.5008.4');
218+
INSERT INTO foorescan values(5009,4,'abc.5009.4');
219+
INSERT INTO foorescan values(5000,5,'abc.5000.5');
220+
INSERT INTO foorescan values(5001,5,'abc.5001.5');
221+
INSERT INTO foorescan values(5002,5,'abc.5002.5');
222+
INSERT INTO foorescan values(5003,5,'abc.5003.5');
223+
INSERT INTO foorescan values(5004,5,'abc.5004.5');
224+
INSERT INTO foorescan values(5005,5,'abc.5005.5');
225+
INSERT INTO foorescan values(5006,5,'abc.5006.5');
226+
INSERT INTO foorescan values(5007,5,'abc.5007.5');
227+
INSERT INTO foorescan values(5008,5,'abc.5008.5');
228+
INSERT INTO foorescan values(5009,5,'abc.5009.5');
229+
CREATE FUNCTION foorescan(int,int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid >= $1 and fooid < $2 ;' LANGUAGE SQL;
230+
--invokes ExecFunctionReScan
231+
SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM foorescan(5002,5004)) ORDER BY 1,2;
232+
fooid | foosubid | fooname
233+
-------+----------+------------
234+
5002 | 1 | abc.5002.1
235+
5002 | 2 | abc.5002.2
236+
5002 | 3 | abc.5002.3
237+
5002 | 4 | abc.5002.4
238+
5002 | 5 | abc.5002.5
239+
5003 | 1 | abc.5003.1
240+
5003 | 2 | abc.5003.2
241+
5003 | 3 | abc.5003.3
242+
5003 | 4 | abc.5003.4
243+
5003 | 5 | abc.5003.5
244+
(10 rows)
245+
246+
CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,5004);
247+
--invokes ExecFunctionReScan
248+
SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM vw_foorescan) ORDER BY 1,2;
249+
fooid | foosubid | fooname
250+
-------+----------+------------
251+
5002 | 1 | abc.5002.1
252+
5002 | 2 | abc.5002.2
253+
5002 | 3 | abc.5002.3
254+
5002 | 4 | abc.5002.4
255+
5002 | 5 | abc.5002.5
256+
5003 | 1 | abc.5003.1
257+
5003 | 2 | abc.5003.2
258+
5003 | 3 | abc.5003.3
259+
5003 | 4 | abc.5003.4
260+
5003 | 5 | abc.5003.5
261+
(10 rows)
262+
263+
CREATE TABLE barrescan (fooid int primary key);
264+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'barrescan_pkey' for table 'barrescan'
265+
INSERT INTO barrescan values(5003);
266+
INSERT INTO barrescan values(5004);
267+
INSERT INTO barrescan values(5005);
268+
INSERT INTO barrescan values(5006);
269+
INSERT INTO barrescan values(5007);
270+
INSERT INTO barrescan values(5008);
271+
CREATE FUNCTION foorescan(int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid = $1;' LANGUAGE SQL;
272+
--invokes ExecFunctionReScan with chgParam != NULL
273+
SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) ORDER BY 1,2;
274+
fooid | foosubid | fooname
275+
-------+----------+------------
276+
5003 | 1 | abc.5003.1
277+
5003 | 2 | abc.5003.2
278+
5003 | 3 | abc.5003.3
279+
5003 | 4 | abc.5003.4
280+
5003 | 5 | abc.5003.5
281+
5004 | 1 | abc.5004.1
282+
5004 | 2 | abc.5004.2
283+
5004 | 3 | abc.5004.3
284+
5004 | 4 | abc.5004.4
285+
5004 | 5 | abc.5004.5
286+
5005 | 1 | abc.5005.1
287+
5005 | 2 | abc.5005.2
288+
5005 | 3 | abc.5005.3
289+
5005 | 4 | abc.5005.4
290+
5005 | 5 | abc.5005.5
291+
5006 | 1 | abc.5006.1
292+
5006 | 2 | abc.5006.2
293+
5006 | 3 | abc.5006.3
294+
5006 | 4 | abc.5006.4
295+
5006 | 5 | abc.5006.5
296+
5007 | 1 | abc.5007.1
297+
5007 | 2 | abc.5007.2
298+
5007 | 3 | abc.5007.3
299+
5007 | 4 | abc.5007.4
300+
5007 | 5 | abc.5007.5
301+
5008 | 1 | abc.5008.1
302+
5008 | 2 | abc.5008.2
303+
5008 | 3 | abc.5008.3
304+
5008 | 4 | abc.5008.4
305+
5008 | 5 | abc.5008.5
306+
(30 rows)
307+
308+
SELECT b.fooid, max(f.foosubid) FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2;
309+
fooid | max
310+
-------+-----
311+
5003 | 5
312+
5004 | 5
313+
5005 | 5
314+
5006 | 5
315+
5007 | 5
316+
5008 | 5
317+
(6 rows)
318+
319+
CREATE VIEW fooview1 AS SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) ORDER BY 1,2;
320+
SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004;
321+
fooid | foosubid | fooname
322+
-------+----------+------------
323+
5004 | 1 | abc.5004.1
324+
5004 | 2 | abc.5004.2
325+
5004 | 3 | abc.5004.3
326+
5004 | 4 | abc.5004.4
327+
5004 | 5 | abc.5004.5
328+
(5 rows)
329+
330+
CREATE VIEW fooview2 AS SELECT b.fooid, max(f.foosubid) AS maxsubid FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2;
331+
SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5;
332+
fooid | maxsubid
333+
-------+----------
334+
5003 | 5
335+
5004 | 5
336+
5005 | 5
337+
5006 | 5
338+
5007 | 5
339+
5008 | 5
340+
(6 rows)
341+
342+
DROP TABLE foorescan;
343+
DROP FUNCTION foorescan(int,int);
344+
DROP VIEW vw_foorescan;
345+
DROP TABLE barrescan;
346+
DROP FUNCTION foorescan(int);
347+
DROP VIEW fooview1;
348+
DROP VIEW fooview2;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp