← Back to branch summary

~ubuntu-branches/ubuntu/hardy/postgresql-8.4/hardy-backports

« back to all changes in this revision

Viewing changes to src/test/regress/sql/rangefuncs.sql

  • Committer: Bazaar Package Importer
  • Author(s): Martin Pitt
  • Date: 2009-03-20 12:00:13 UTC
  • Revision ID: james.westby@ubuntu.com-20090320120013-hogj7egc5mjncc5g
Tags: upstream-8.4~0cvs20090328
Import upstream version 8.4~0cvs20090328

Show diffs side-by-side

added added

removed removed

Lines of Context:
src/test/regress/sql/rangefuncs.sql
 
1
SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%';
 
2
 
 
3
CREATE TABLE foo2(fooid int, f2 int);
 
4
INSERT INTO foo2 VALUES(1, 11);
 
5
INSERT INTO foo2 VALUES(2, 22);
 
6
INSERT INTO foo2 VALUES(1, 111);
 
7
 
 
8
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
 
9
 
 
10
-- supposed to fail with ERROR
 
11
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
 
12
 
 
13
-- function in subselect
 
14
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
 
15
 
 
16
-- function in subselect
 
17
select * from foo2 where f2 in (select f2 from foot(1) z where z.fooid = foo2.fooid) ORDER BY 1,2;
 
18
 
 
19
-- function in subselect
 
20
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = 1) ORDER BY 1,2;
 
21
 
 
22
-- nested functions
 
23
select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2;
 
24
 
 
25
CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
 
26
INSERT INTO foo VALUES(1,1,'Joe');
 
27
INSERT INTO foo VALUES(1,2,'Ed');
 
28
INSERT INTO foo VALUES(2,1,'Mary');
 
29
 
 
30
-- sql, proretset = f, prorettype = b
 
31
CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
 
32
SELECT * FROM getfoo(1) AS t1;
 
33
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
 
34
SELECT * FROM vw_getfoo;
 
35
 
 
36
-- sql, proretset = t, prorettype = b
 
37
DROP VIEW vw_getfoo;
 
38
DROP FUNCTION getfoo(int);
 
39
CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
 
40
SELECT * FROM getfoo(1) AS t1;
 
41
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
 
42
SELECT * FROM vw_getfoo;
 
43
 
 
44
-- sql, proretset = t, prorettype = b
 
45
DROP VIEW vw_getfoo;
 
46
DROP FUNCTION getfoo(int);
 
47
CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
 
48
SELECT * FROM getfoo(1) AS t1;
 
49
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
 
50
SELECT * FROM vw_getfoo;
 
51
 
 
52
-- sql, proretset = f, prorettype = c
 
53
DROP VIEW vw_getfoo;
 
54
DROP FUNCTION getfoo(int);
 
55
CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
 
56
SELECT * FROM getfoo(1) AS t1;
 
57
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
 
58
SELECT * FROM vw_getfoo;
 
59
 
 
60
-- sql, proretset = t, prorettype = c
 
61
DROP VIEW vw_getfoo;
 
62
DROP FUNCTION getfoo(int);
 
63
CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
 
64
SELECT * FROM getfoo(1) AS t1;
 
65
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
 
66
SELECT * FROM vw_getfoo;
 
67
 
 
68
-- sql, proretset = f, prorettype = record
 
69
DROP VIEW vw_getfoo;
 
70
DROP FUNCTION getfoo(int);
 
71
CREATE FUNCTION getfoo(int) RETURNS RECORD AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
 
72
SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
 
73
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS 
 
74
(fooid int, foosubid int, fooname text);
 
75
SELECT * FROM vw_getfoo;
 
76
 
 
77
-- sql, proretset = t, prorettype = record
 
78
DROP VIEW vw_getfoo;
 
79
DROP FUNCTION getfoo(int);
 
80
CREATE FUNCTION getfoo(int) RETURNS setof record AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
 
81
SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
 
82
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS
 
83
(fooid int, foosubid int, fooname text);
 
84
SELECT * FROM vw_getfoo;
 
85
 
 
86
-- plpgsql, proretset = f, prorettype = b
 
87
DROP VIEW vw_getfoo;
 
88
DROP FUNCTION getfoo(int);
 
89
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;
 
90
SELECT * FROM getfoo(1) AS t1;
 
91
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
 
92
SELECT * FROM vw_getfoo;
 
93
 
 
94
-- plpgsql, proretset = f, prorettype = c
 
95
DROP VIEW vw_getfoo;
 
96
DROP FUNCTION getfoo(int);
 
97
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;
 
98
SELECT * FROM getfoo(1) AS t1;
 
99
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
 
100
SELECT * FROM vw_getfoo;
 
101
 
 
102
DROP VIEW vw_getfoo;
 
103
DROP FUNCTION getfoo(int);
 
104
DROP FUNCTION foot(int);
 
105
DROP TABLE foo2;
 
106
DROP TABLE foo;
 
107
 
 
108
-- Rescan tests --
 
109
CREATE TABLE foorescan (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
 
110
INSERT INTO foorescan values(5000,1,'abc.5000.1');
 
111
INSERT INTO foorescan values(5001,1,'abc.5001.1');
 
112
INSERT INTO foorescan values(5002,1,'abc.5002.1');
 
113
INSERT INTO foorescan values(5003,1,'abc.5003.1');
 
114
INSERT INTO foorescan values(5004,1,'abc.5004.1');
 
115
INSERT INTO foorescan values(5005,1,'abc.5005.1');
 
116
INSERT INTO foorescan values(5006,1,'abc.5006.1');
 
117
INSERT INTO foorescan values(5007,1,'abc.5007.1');
 
118
INSERT INTO foorescan values(5008,1,'abc.5008.1');
 
119
INSERT INTO foorescan values(5009,1,'abc.5009.1');
 
120
 
 
121
INSERT INTO foorescan values(5000,2,'abc.5000.2');
 
122
INSERT INTO foorescan values(5001,2,'abc.5001.2');
 
123
INSERT INTO foorescan values(5002,2,'abc.5002.2');
 
124
INSERT INTO foorescan values(5003,2,'abc.5003.2');
 
125
INSERT INTO foorescan values(5004,2,'abc.5004.2');
 
126
INSERT INTO foorescan values(5005,2,'abc.5005.2');
 
127
INSERT INTO foorescan values(5006,2,'abc.5006.2');
 
128
INSERT INTO foorescan values(5007,2,'abc.5007.2');
 
129
INSERT INTO foorescan values(5008,2,'abc.5008.2');
 
130
INSERT INTO foorescan values(5009,2,'abc.5009.2');
 
131
 
 
132
INSERT INTO foorescan values(5000,3,'abc.5000.3');
 
133
INSERT INTO foorescan values(5001,3,'abc.5001.3');
 
134
INSERT INTO foorescan values(5002,3,'abc.5002.3');
 
135
INSERT INTO foorescan values(5003,3,'abc.5003.3');
 
136
INSERT INTO foorescan values(5004,3,'abc.5004.3');
 
137
INSERT INTO foorescan values(5005,3,'abc.5005.3');
 
138
INSERT INTO foorescan values(5006,3,'abc.5006.3');
 
139
INSERT INTO foorescan values(5007,3,'abc.5007.3');
 
140
INSERT INTO foorescan values(5008,3,'abc.5008.3');
 
141
INSERT INTO foorescan values(5009,3,'abc.5009.3');
 
142
 
 
143
INSERT INTO foorescan values(5000,4,'abc.5000.4');
 
144
INSERT INTO foorescan values(5001,4,'abc.5001.4');
 
145
INSERT INTO foorescan values(5002,4,'abc.5002.4');
 
146
INSERT INTO foorescan values(5003,4,'abc.5003.4');
 
147
INSERT INTO foorescan values(5004,4,'abc.5004.4');
 
148
INSERT INTO foorescan values(5005,4,'abc.5005.4');
 
149
INSERT INTO foorescan values(5006,4,'abc.5006.4');
 
150
INSERT INTO foorescan values(5007,4,'abc.5007.4');
 
151
INSERT INTO foorescan values(5008,4,'abc.5008.4');
 
152
INSERT INTO foorescan values(5009,4,'abc.5009.4');
 
153
 
 
154
INSERT INTO foorescan values(5000,5,'abc.5000.5');
 
155
INSERT INTO foorescan values(5001,5,'abc.5001.5');
 
156
INSERT INTO foorescan values(5002,5,'abc.5002.5');
 
157
INSERT INTO foorescan values(5003,5,'abc.5003.5');
 
158
INSERT INTO foorescan values(5004,5,'abc.5004.5');
 
159
INSERT INTO foorescan values(5005,5,'abc.5005.5');
 
160
INSERT INTO foorescan values(5006,5,'abc.5006.5');
 
161
INSERT INTO foorescan values(5007,5,'abc.5007.5');
 
162
INSERT INTO foorescan values(5008,5,'abc.5008.5');
 
163
INSERT INTO foorescan values(5009,5,'abc.5009.5');
 
164
 
 
165
CREATE FUNCTION foorescan(int,int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid >= $1 and fooid < $2 ;' LANGUAGE SQL;
 
166
 
 
167
--invokes ExecFunctionReScan
 
168
SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM foorescan(5002,5004)) ORDER BY 1,2;
 
169
 
 
170
CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,5004);
 
171
 
 
172
--invokes ExecFunctionReScan
 
173
SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM vw_foorescan) ORDER BY 1,2;
 
174
 
 
175
CREATE TABLE barrescan (fooid int primary key);
 
176
INSERT INTO barrescan values(5003);
 
177
INSERT INTO barrescan values(5004);
 
178
INSERT INTO barrescan values(5005);
 
179
INSERT INTO barrescan values(5006);
 
180
INSERT INTO barrescan values(5007);
 
181
INSERT INTO barrescan values(5008);
 
182
 
 
183
CREATE FUNCTION foorescan(int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid = $1;' LANGUAGE SQL;
 
184
 
 
185
--invokes ExecFunctionReScan with chgParam != NULL
 
186
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;
 
187
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;
 
188
 
 
189
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;
 
190
SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004;
 
191
 
 
192
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;
 
193
SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5;
 
194
 
 
195
DROP VIEW vw_foorescan;
 
196
DROP VIEW fooview1;
 
197
DROP VIEW fooview2;
 
198
DROP FUNCTION foorescan(int,int);
 
199
DROP FUNCTION foorescan(int);
 
200
DROP TABLE foorescan;
 
201
DROP TABLE barrescan;
 
202
 
 
203
--
 
204
-- Test cases involving OUT parameters
 
205
--
 
206
 
 
207
CREATE FUNCTION foo(in f1 int, out f2 int)
 
208
AS 'select $1+1' LANGUAGE sql;
 
209
SELECT foo(42);
 
210
SELECT * FROM foo(42);
 
211
SELECT * FROM foo(42) AS p(x);
 
212
 
 
213
-- explicit spec of return type is OK
 
214
CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int) RETURNS int
 
215
AS 'select $1+1' LANGUAGE sql;
 
216
-- error, wrong result type
 
217
CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int) RETURNS float
 
218
AS 'select $1+1' LANGUAGE sql;
 
219
-- with multiple OUT params you must get a RECORD result
 
220
CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int, out f3 text) RETURNS int
 
221
AS 'select $1+1' LANGUAGE sql;
 
222
CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int, out f3 text)
 
223
RETURNS record
 
224
AS 'select $1+1' LANGUAGE sql;
 
225
 
 
226
CREATE OR REPLACE FUNCTION foor(in f1 int, out f2 int, out text)
 
227
AS $$select $1-1, $1::text || 'z'$$ LANGUAGE sql;
 
228
SELECT f1, foor(f1) FROM int4_tbl;
 
229
SELECT * FROM foor(42);
 
230
SELECT * FROM foor(42) AS p(a,b);
 
231
 
 
232
CREATE OR REPLACE FUNCTION foob(in f1 int, inout f2 int, out text)
 
233
AS $$select $2-1, $1::text || 'z'$$ LANGUAGE sql;
 
234
SELECT f1, foob(f1, f1/2) FROM int4_tbl;
 
235
SELECT * FROM foob(42, 99);
 
236
SELECT * FROM foob(42, 99) AS p(a,b);
 
237
 
 
238
-- Can reference function with or without OUT params for DROP, etc
 
239
DROP FUNCTION foo(int);
 
240
DROP FUNCTION foor(in f2 int, out f1 int, out text);
 
241
DROP FUNCTION foob(in f1 int, inout f2 int);
 
242
 
 
243
--
 
244
-- For my next trick, polymorphic OUT parameters
 
245
--
 
246
 
 
247
CREATE FUNCTION dup (f1 anyelement, f2 out anyelement, f3 out anyarray)
 
248
AS 'select $1, array[$1,$1]' LANGUAGE sql;
 
249
SELECT dup(22);
 
250
SELECT dup('xyz');      -- fails
 
251
SELECT dup('xyz'::text);
 
252
SELECT * FROM dup('xyz'::text);
 
253
 
 
254
-- equivalent specification
 
255
CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)
 
256
AS 'select $1, array[$1,$1]' LANGUAGE sql;
 
257
SELECT dup(22);
 
258
 
 
259
DROP FUNCTION dup(anyelement);
 
260
 
 
261
-- fails, no way to deduce outputs
 
262
CREATE FUNCTION bad (f1 int, out f2 anyelement, out f3 anyarray)
 
263
AS 'select $1, array[$1,$1]' LANGUAGE sql;
 
264
 
 
265
--
 
266
-- table functions
 
267
--
 
268
 
 
269
CREATE OR REPLACE FUNCTION foo()
 
270
RETURNS TABLE(a int)
 
271
AS $$ SELECT a FROM generate_series(1,5) a(a) $$ LANGUAGE sql;
 
272
SELECT * FROM foo();
 
273
DROP FUNCTION foo();
 
274
 
 
275
CREATE OR REPLACE FUNCTION foo(int)
 
276
RETURNS TABLE(a int, b int)
 
277
AS $$ SELECT a, b
 
278
         FROM generate_series(1,$1) a(a),
 
279
              generate_series(1,$1) b(b) $$ LANGUAGE sql;
 
280
SELECT * FROM foo(3);
 
281
DROP FUNCTION foo(int);
 
282
 
 
283
--
 
284
-- some tests on SQL functions with RETURNING
 
285
--
 
286
 
 
287
create temp table tt(f1 serial, data text);
 
288
 
 
289
create function insert_tt(text) returns int as
 
290
$$ insert into tt(data) values($1) returning f1 $$
 
291
language sql;
 
292
 
 
293
select insert_tt('foo');
 
294
select insert_tt('bar');
 
295
select * from tt;
 
296
 
 
297
-- insert will execute to completion even if function needs just 1 row
 
298
create or replace function insert_tt(text) returns int as
 
299
$$ insert into tt(data) values($1),($1||$1) returning f1 $$
 
300
language sql;
 
301
 
 
302
select insert_tt('fool');
 
303
select * from tt;
 
304
 
 
305
-- setof does what's expected
 
306
create or replace function insert_tt2(text,text) returns setof int as
 
307
$$ insert into tt(data) values($1),($2) returning f1 $$
 
308
language sql;
 
309
 
 
310
select insert_tt2('foolish','barrish');
 
311
select * from insert_tt2('baz','quux');
 
312
select * from tt;
 
313
 
 
314
-- limit doesn't prevent execution to completion
 
315
select insert_tt2('foolish','barrish') limit 1;
 
316
select * from tt;
 
317
 
 
318
-- triggers will fire, too
 
319
create function noticetrigger() returns trigger as $$
 
320
begin
 
321
  raise notice 'noticetrigger % %', new.f1, new.data;
 
322
  return null;
 
323
end $$ language plpgsql;
 
324
create trigger tnoticetrigger after insert on tt for each row
 
325
execute procedure noticetrigger();
 
326
 
 
327
select insert_tt2('foolme','barme') limit 1;
 
328
select * from tt;
 
329
 
 
330
-- and rules work
 
331
create temp table tt_log(f1 int, data text);
 
332
 
 
333
create rule insert_tt_rule as on insert to tt do also
 
334
  insert into tt_log values(new.*);
 
335
 
 
336
select insert_tt2('foollog','barlog') limit 1;
 
337
select * from tt;
 
338
-- note that nextval() gets executed a second time in the rule expansion,
 
339
-- which is expected.
 
340
select * from tt_log;