1 -- Currently this tests polymorphic aggregates and indirectly does some
2 -- testing of polymorphic SQL functions. It ought to be extended.
3 -- Tests for other features related to function-calling have snuck in, too.
9 -- P = type is polymorphic
10 -- N = type is non-polymorphic
11 -- B = aggregate base type
12 -- S = aggregate state type
13 -- R = aggregate return type
14 -- 1 = arg1 of a function
15 -- 2 = arg2 of a function
17 -- tf = trans (state) function
18 -- ff = final function
19 -- rt = return type of a function
29 -- when (B = A) -> (tf2 = NE)
33 -- tf2 = (NE || P || N)
36 -- create functions for use as tf and ff with the needed combinations of
37 -- argument polymorphism, but within the constraints of valid aggregate
38 -- functions, i.e. tf arg1 and tf return type must match
40 -- polymorphic single arg transfn
41 CREATE FUNCTION stfp(anyarray) RETURNS anyarray AS
42 'select $1' LANGUAGE SQL;
43 -- non-polymorphic single arg transfn
44 CREATE FUNCTION stfnp(int[]) RETURNS int[] AS
45 'select $1' LANGUAGE SQL;
47 -- dual polymorphic transfn
48 CREATE FUNCTION tfp(anyarray,anyelement) RETURNS anyarray AS
49 'select $1 || $2' LANGUAGE SQL;
50 -- dual non-polymorphic transfn
51 CREATE FUNCTION tfnp(int[],int) RETURNS int[] AS
52 'select $1 || $2' LANGUAGE SQL;
54 -- arg1 only polymorphic transfn
55 CREATE FUNCTION tf1p(anyarray,int) RETURNS anyarray AS
56 'select $1' LANGUAGE SQL;
57 -- arg2 only polymorphic transfn
58 CREATE FUNCTION tf2p(int[],anyelement) RETURNS int[] AS
59 'select $1' LANGUAGE SQL;
61 -- multi-arg polymorphic
62 CREATE FUNCTION sum3(anyelement,anyelement,anyelement) returns anyelement AS
63 'select $1+$2+$3' language sql strict;
65 -- finalfn polymorphic
66 CREATE FUNCTION ffp(anyarray) RETURNS anyarray AS
67 'select $1' LANGUAGE SQL;
68 -- finalfn non-polymorphic
69 CREATE FUNCTION ffnp(int[]) returns int[] as
70 'select $1' LANGUAGE SQL;
72 -- Try to cover all the possible states:
74 -- Note: in Cases 1 & 2, we are trying to return P. Therefore, if the transfn
75 -- is stfnp, tfnp, or tf2p, we must use ffp as finalfn, because stfnp, tfnp,
76 -- and tf2p do not return P. Conversely, in Cases 3 & 4, we are trying to
77 -- return N. Therefore, if the transfn is stfp, tfp, or tf1p, we must use ffnp
78 -- as finalfn, because stfp, tfp, and tf1p do not return N.
80 -- Case1 (R = P) && (B = A)
81 -- ------------------------
86 CREATE AGGREGATE myaggp01a(*) (SFUNC = stfnp, STYPE = int4[],
87 FINALFUNC = ffp, INITCOND = '{}');
90 -- should ERROR: stfnp(anyarray) not matched by stfnp(int[])
91 CREATE AGGREGATE myaggp02a(*) (SFUNC = stfnp, STYPE = anyarray,
92 FINALFUNC = ffp, INITCOND = '{}');
96 CREATE AGGREGATE myaggp03a(*) (SFUNC = stfp, STYPE = int4[],
97 FINALFUNC = ffp, INITCOND = '{}');
98 CREATE AGGREGATE myaggp03b(*) (SFUNC = stfp, STYPE = int4[],
102 -- should ERROR: we have no way to resolve S
103 CREATE AGGREGATE myaggp04a(*) (SFUNC = stfp, STYPE = anyarray,
104 FINALFUNC = ffp, INITCOND = '{}');
105 CREATE AGGREGATE myaggp04b(*) (SFUNC = stfp, STYPE = anyarray,
109 -- Case2 (R = P) && ((B = P) || (B = N))
110 -- -------------------------------------
112 -- -----------------------
115 CREATE AGGREGATE myaggp05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[],
116 FINALFUNC = ffp, INITCOND = '{}');
120 CREATE AGGREGATE myaggp06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[],
121 FINALFUNC = ffp, INITCOND = '{}');
124 -- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int)
125 CREATE AGGREGATE myaggp07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[],
126 FINALFUNC = ffp, INITCOND = '{}');
130 CREATE AGGREGATE myaggp08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[],
131 FINALFUNC = ffp, INITCOND = '{}');
135 CREATE AGGREGATE myaggp09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[],
136 FINALFUNC = ffp, INITCOND = '{}');
137 CREATE AGGREGATE myaggp09b(BASETYPE = int, SFUNC = tf1p, STYPE = int[],
142 CREATE AGGREGATE myaggp10a(BASETYPE = int, SFUNC = tfp, STYPE = int[],
143 FINALFUNC = ffp, INITCOND = '{}');
144 CREATE AGGREGATE myaggp10b(BASETYPE = int, SFUNC = tfp, STYPE = int[],
148 -- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int)
149 CREATE AGGREGATE myaggp11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[],
150 FINALFUNC = ffp, INITCOND = '{}');
151 CREATE AGGREGATE myaggp11b(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[],
155 -- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement)
156 CREATE AGGREGATE myaggp12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[],
157 FINALFUNC = ffp, INITCOND = '{}');
158 CREATE AGGREGATE myaggp12b(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[],
162 -- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int)
163 CREATE AGGREGATE myaggp13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray,
164 FINALFUNC = ffp, INITCOND = '{}');
167 -- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement)
168 CREATE AGGREGATE myaggp14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray,
169 FINALFUNC = ffp, INITCOND = '{}');
172 -- should ERROR: tfnp(anyarray, anyelement) not matched by tfnp(int[],int)
173 CREATE AGGREGATE myaggp15a(BASETYPE = anyelement, SFUNC = tfnp,
174 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}');
177 -- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement)
178 CREATE AGGREGATE myaggp16a(BASETYPE = anyelement, SFUNC = tf2p,
179 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}');
182 -- should ERROR: we have no way to resolve S
183 CREATE AGGREGATE myaggp17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray,
184 FINALFUNC = ffp, INITCOND = '{}');
185 CREATE AGGREGATE myaggp17b(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray,
189 -- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement)
190 CREATE AGGREGATE myaggp18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray,
191 FINALFUNC = ffp, INITCOND = '{}');
192 CREATE AGGREGATE myaggp18b(BASETYPE = int, SFUNC = tfp, STYPE = anyarray,
196 -- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int)
197 CREATE AGGREGATE myaggp19a(BASETYPE = anyelement, SFUNC = tf1p,
198 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}');
199 CREATE AGGREGATE myaggp19b(BASETYPE = anyelement, SFUNC = tf1p,
200 STYPE = anyarray, INITCOND = '{}');
204 CREATE AGGREGATE myaggp20a(BASETYPE = anyelement, SFUNC = tfp,
205 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}');
206 CREATE AGGREGATE myaggp20b(BASETYPE = anyelement, SFUNC = tfp,
207 STYPE = anyarray, INITCOND = '{}');
209 -- Case3 (R = N) && (B = A)
210 -- ------------------------
215 CREATE AGGREGATE myaggn01a(*) (SFUNC = stfnp, STYPE = int4[],
216 FINALFUNC = ffnp, INITCOND = '{}');
217 CREATE AGGREGATE myaggn01b(*) (SFUNC = stfnp, STYPE = int4[],
221 -- should ERROR: stfnp(anyarray) not matched by stfnp(int[])
222 CREATE AGGREGATE myaggn02a(*) (SFUNC = stfnp, STYPE = anyarray,
223 FINALFUNC = ffnp, INITCOND = '{}');
224 CREATE AGGREGATE myaggn02b(*) (SFUNC = stfnp, STYPE = anyarray,
229 CREATE AGGREGATE myaggn03a(*) (SFUNC = stfp, STYPE = int4[],
230 FINALFUNC = ffnp, INITCOND = '{}');
233 -- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
234 CREATE AGGREGATE myaggn04a(*) (SFUNC = stfp, STYPE = anyarray,
235 FINALFUNC = ffnp, INITCOND = '{}');
238 -- Case4 (R = N) && ((B = P) || (B = N))
239 -- -------------------------------------
241 -- -----------------------
244 CREATE AGGREGATE myaggn05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[],
245 FINALFUNC = ffnp, INITCOND = '{}');
246 CREATE AGGREGATE myaggn05b(BASETYPE = int, SFUNC = tfnp, STYPE = int[],
251 CREATE AGGREGATE myaggn06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[],
252 FINALFUNC = ffnp, INITCOND = '{}');
253 CREATE AGGREGATE myaggn06b(BASETYPE = int, SFUNC = tf2p, STYPE = int[],
257 -- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int)
258 CREATE AGGREGATE myaggn07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[],
259 FINALFUNC = ffnp, INITCOND = '{}');
260 CREATE AGGREGATE myaggn07b(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[],
265 CREATE AGGREGATE myaggn08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[],
266 FINALFUNC = ffnp, INITCOND = '{}');
267 CREATE AGGREGATE myaggn08b(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[],
272 CREATE AGGREGATE myaggn09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[],
273 FINALFUNC = ffnp, INITCOND = '{}');
277 CREATE AGGREGATE myaggn10a(BASETYPE = int, SFUNC = tfp, STYPE = int[],
278 FINALFUNC = ffnp, INITCOND = '{}');
281 -- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int)
282 CREATE AGGREGATE myaggn11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[],
283 FINALFUNC = ffnp, INITCOND = '{}');
286 -- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement)
287 CREATE AGGREGATE myaggn12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[],
288 FINALFUNC = ffnp, INITCOND = '{}');
291 -- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int)
292 CREATE AGGREGATE myaggn13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray,
293 FINALFUNC = ffnp, INITCOND = '{}');
294 CREATE AGGREGATE myaggn13b(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray,
298 -- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement)
299 CREATE AGGREGATE myaggn14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray,
300 FINALFUNC = ffnp, INITCOND = '{}');
301 CREATE AGGREGATE myaggn14b(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray,
305 -- should ERROR: tfnp(anyarray, anyelement) not matched by tfnp(int[],int)
306 CREATE AGGREGATE myaggn15a(BASETYPE = anyelement, SFUNC = tfnp,
307 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}');
308 CREATE AGGREGATE myaggn15b(BASETYPE = anyelement, SFUNC = tfnp,
309 STYPE = anyarray, INITCOND = '{}');
312 -- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement)
313 CREATE AGGREGATE myaggn16a(BASETYPE = anyelement, SFUNC = tf2p,
314 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}');
315 CREATE AGGREGATE myaggn16b(BASETYPE = anyelement, SFUNC = tf2p,
316 STYPE = anyarray, INITCOND = '{}');
319 -- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
320 CREATE AGGREGATE myaggn17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray,
321 FINALFUNC = ffnp, INITCOND = '{}');
324 -- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement)
325 CREATE AGGREGATE myaggn18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray,
326 FINALFUNC = ffnp, INITCOND = '{}');
329 -- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int)
330 CREATE AGGREGATE myaggn19a(BASETYPE = anyelement, SFUNC = tf1p,
331 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}');
334 -- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
335 CREATE AGGREGATE myaggn20a(BASETYPE = anyelement, SFUNC = tfp,
336 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}');
338 -- multi-arg polymorphic
339 CREATE AGGREGATE mysum2(anyelement,anyelement) (SFUNC = sum3,
340 STYPE = anyelement, INITCOND = '0');
342 -- create test data for polymorphic aggregates
343 create temp table t(f1 int, f2 int[], f3 text);
344 insert into t values(1,array[1],'a');
345 insert into t values(1,array[11],'b');
346 insert into t values(1,array[111],'c');
347 insert into t values(2,array[2],'a');
348 insert into t values(2,array[22],'b');
349 insert into t values(2,array[222],'c');
350 insert into t values(3,array[3],'a');
351 insert into t values(3,array[3],'b');
353 -- test the successfully created polymorphic aggregates
354 select f3, myaggp01a(*) from t group by f3 order by f3;
355 select f3, myaggp03a(*) from t group by f3 order by f3;
356 select f3, myaggp03b(*) from t group by f3 order by f3;
357 select f3, myaggp05a(f1) from t group by f3 order by f3;
358 select f3, myaggp06a(f1) from t group by f3 order by f3;
359 select f3, myaggp08a(f1) from t group by f3 order by f3;
360 select f3, myaggp09a(f1) from t group by f3 order by f3;
361 select f3, myaggp09b(f1) from t group by f3 order by f3;
362 select f3, myaggp10a(f1) from t group by f3 order by f3;
363 select f3, myaggp10b(f1) from t group by f3 order by f3;
364 select f3, myaggp20a(f1) from t group by f3 order by f3;
365 select f3, myaggp20b(f1) from t group by f3 order by f3;
366 select f3, myaggn01a(*) from t group by f3 order by f3;
367 select f3, myaggn01b(*) from t group by f3 order by f3;
368 select f3, myaggn03a(*) from t group by f3 order by f3;
369 select f3, myaggn05a(f1) from t group by f3 order by f3;
370 select f3, myaggn05b(f1) from t group by f3 order by f3;
371 select f3, myaggn06a(f1) from t group by f3 order by f3;
372 select f3, myaggn06b(f1) from t group by f3 order by f3;
373 select f3, myaggn08a(f1) from t group by f3 order by f3;
374 select f3, myaggn08b(f1) from t group by f3 order by f3;
375 select f3, myaggn09a(f1) from t group by f3 order by f3;
376 select f3, myaggn10a(f1) from t group by f3 order by f3;
377 select mysum2(f1, f1 + 1) from t;
379 -- test inlining of polymorphic SQL functions
380 create function bleat(int) returns int as $$
382 raise notice 'bleat %', $1;
384 end$$ language plpgsql;
386 create function sql_if(bool, anyelement, anyelement) returns anyelement as $$
387 select case when $1 then $2 else $3 end $$ language sql;
389 -- Note this would fail with integer overflow, never mind wrong bleat() output,
390 -- if the CASE expression were not successfully inlined
391 select f1, sql_if(f1 > 0, bleat(f1), bleat(f1 + 1)) from int4_tbl;
393 select q2, sql_if(q2 > 0, q2, q2 + 1) from int8_tbl;
395 -- another sort of polymorphic aggregate
397 CREATE AGGREGATE array_cat_accum (anyarray)
404 SELECT array_cat_accum(i)
405 FROM (VALUES (ARRAY[1,2]), (ARRAY[3,4])) as t(i);
407 SELECT array_cat_accum(i)
408 FROM (VALUES (ARRAY[row(1,2),row(3,4)]), (ARRAY[row(5,6),row(7,8)])) as t(i);
410 -- another kind of polymorphic aggregate
412 create function add_group(grp anyarray, ad anyelement, size integer)
419 if array_upper(grp, 1) < size then
425 language plpgsql immutable;
427 create aggregate build_group(anyelement, integer) (
432 select build_group(q1,3) from int8_tbl;
434 -- this should fail because stype isn't compatible with arg
435 create aggregate build_group(int8, integer) (
440 -- but we can make a non-poly agg from a poly sfunc if types are OK
441 create aggregate build_group(int8, integer) (
446 -- check that we can apply functions taking ANYARRAY to pg_stats
447 select distinct array_ndims(histogram_bounds) from pg_stats
448 where histogram_bounds is not null;
450 -- such functions must protect themselves if varying element type isn't OK
451 -- (WHERE clause here is to avoid possibly getting a collation error instead)
452 select max(histogram_bounds) from pg_stats where tablename = 'pg_am';
454 -- test variadic polymorphic functions
456 create function myleast(variadic anyarray) returns anyelement as $$
457 select min($1[i]) from generate_subscripts($1,1) g(i)
458 $$ language sql immutable strict;
460 select myleast(10, 1, 20, 33);
461 select myleast(1.1, 0.22, 0.55);
462 select myleast('z'::text);
463 select myleast(); -- fail
465 -- test with variadic call parameter
466 select myleast(variadic array[1,2,3,4,-1]);
467 select myleast(variadic array[1.1, -5.5]);
469 --test with empty variadic call parameter
470 select myleast(variadic array[]::int[]);
472 -- an example with some ordinary arguments too
473 create function concat(text, variadic anyarray) returns text as $$
474 select array_to_string($2, $1);
475 $$ language sql immutable strict;
477 select concat('%', 1, 2, 3, 4, 5);
478 select concat('|', 'a'::text, 'b', 'c');
479 select concat('|', variadic array[1,2,33]);
480 select concat('|', variadic array[]::int[]);
482 drop function concat(text, anyarray);
484 -- mix variadic with anyelement
485 create function formarray(anyelement, variadic anyarray) returns anyarray as $$
486 select array_prepend($1, $2);
487 $$ language sql immutable strict;
489 select formarray(1,2,3,4,5);
490 select formarray(1.1, variadic array[1.2,55.5]);
491 select formarray(1.1, array[1.2,55.5]); -- fail without variadic
492 select formarray(1, 'x'::text); -- fail, type mismatch
493 select formarray(1, variadic array['x'::text]); -- fail, type mismatch
495 drop function formarray(anyelement, variadic anyarray);
497 -- test pg_typeof() function
498 select pg_typeof(null); -- unknown
499 select pg_typeof(0); -- integer
500 select pg_typeof(0.0); -- numeric
501 select pg_typeof(1+1 = 2); -- boolean
502 select pg_typeof('x'); -- unknown
503 select pg_typeof('' || ''); -- text
504 select pg_typeof(pg_typeof(0)); -- regtype
505 select pg_typeof(array[1.2,55.5]); -- numeric[]
506 select pg_typeof(myleast(10, 1, 20, 33)); -- polymorphic input
508 -- test functions with default parameters
510 -- test basic functionality
511 create function dfunc(a int = 1, int = 2) returns int as $$
517 select dfunc(10, 20);
518 select dfunc(10, 20, 30); -- fail
520 drop function dfunc(); -- fail
521 drop function dfunc(int); -- fail
522 drop function dfunc(int, int); -- ok
524 -- fail: defaults must be at end of argument list
525 create function dfunc(a int = 1, b int) returns int as $$
529 -- however, this should work:
530 create function dfunc(a int = 1, out sum int, b int = 2) as $$
536 -- verify it lists properly
539 drop function dfunc(int, int);
541 -- check implicit coercion
542 create function dfunc(a int DEFAULT 1.0, int DEFAULT '-1') returns int as $$
547 create function dfunc(a text DEFAULT 'Hello', b text DEFAULT 'World') returns text as $$
548 select $1 || ', ' || $2;
551 select dfunc(); -- fail: which dfunc should be called? int or text
552 select dfunc('Hi'); -- ok
553 select dfunc('Hi', 'City'); -- ok
554 select dfunc(0); -- ok
555 select dfunc(10, 20); -- ok
557 drop function dfunc(int, int);
558 drop function dfunc(text, text);
560 create function dfunc(int = 1, int = 2) returns int as $$
564 create function dfunc(int = 1, int = 2, int = 3, int = 4) returns int as $$
568 -- Now, dfunc(nargs = 2) and dfunc(nargs = 4) are ambiguous when called
569 -- with 0 to 2 arguments.
571 select dfunc(); -- fail
572 select dfunc(1); -- fail
573 select dfunc(1, 2); -- fail
574 select dfunc(1, 2, 3); -- ok
575 select dfunc(1, 2, 3, 4); -- ok
577 drop function dfunc(int, int);
578 drop function dfunc(int, int, int, int);
580 -- default values are not allowed for output parameters
581 create function dfunc(out int = 20) returns int as $$
585 -- polymorphic parameter test
586 create function dfunc(anyelement = 'World'::text) returns text as $$
587 select 'Hello, ' || $1::text;
592 select dfunc(to_date('20081215','YYYYMMDD'));
593 select dfunc('City'::text);
595 drop function dfunc(anyelement);
597 -- check defaults for variadics
599 create function dfunc(a variadic int[]) returns int as
600 $$ select array_upper($1, 1) $$ language sql;
602 select dfunc(); -- fail
606 create or replace function dfunc(a variadic int[] default array[]::int[]) returns int as
607 $$ select array_upper($1, 1) $$ language sql;
609 select dfunc(); -- now ok
613 -- can't remove the default once it exists
614 create or replace function dfunc(a variadic int[]) returns int as
615 $$ select array_upper($1, 1) $$ language sql;
619 drop function dfunc(a variadic int[]);
621 -- Ambiguity should be reported only if there's not a better match available
623 create function dfunc(int = 1, int = 2, int = 3) returns int as $$
627 create function dfunc(int = 1, int = 2) returns int as $$
631 create function dfunc(text) returns text as $$
635 -- dfunc(narg=2) and dfunc(narg=3) are ambiguous
636 select dfunc(1); -- fail
638 -- but this works since the ambiguous functions aren't preferred anyway
641 drop function dfunc(int, int, int);
642 drop function dfunc(int, int);
643 drop function dfunc(text);
646 -- Tests for named- and mixed-notation function calling
649 create function dfunc(a int, b int, c int = 0, d int = 0)
650 returns table (a int, b int, c int, d int) as $$
651 select $1, $2, $3, $4;
654 select (dfunc(10,20,30)).*;
655 select (dfunc(a := 10, b := 20, c := 30)).*;
656 select * from dfunc(a := 10, b := 20);
657 select * from dfunc(b := 10, a := 20);
658 select * from dfunc(0); -- fail
659 select * from dfunc(1,2);
660 select * from dfunc(1,2,c := 3);
661 select * from dfunc(1,2,d := 3);
663 select * from dfunc(x := 20, b := 10, x := 30); -- fail, duplicate name
664 select * from dfunc(10, b := 20, 30); -- fail, named args must be last
665 select * from dfunc(x := 10, b := 20, c := 30); -- fail, unknown param
666 select * from dfunc(10, 10, a := 20); -- fail, a overlaps positional parameter
667 select * from dfunc(1,c := 2,d := 3); -- fail, no value for b
669 drop function dfunc(int, int, int, int);
671 -- test with different parameter types
672 create function dfunc(a varchar, b numeric, c date = current_date)
673 returns table (a varchar, b numeric, c date) as $$
677 select (dfunc('Hello World', 20, '2009-07-25'::date)).*;
678 select * from dfunc('Hello World', 20, '2009-07-25'::date);
679 select * from dfunc(c := '2009-07-25'::date, a := 'Hello World', b := 20);
680 select * from dfunc('Hello World', b := 20, c := '2009-07-25'::date);
681 select * from dfunc('Hello World', c := '2009-07-25'::date, b := 20);
682 select * from dfunc('Hello World', c := 20, b := '2009-07-25'::date); -- fail
684 drop function dfunc(varchar, numeric, date);
686 -- test out parameters with named params
687 create function dfunc(a varchar = 'def a', out _a varchar, c numeric = NULL, out _c numeric)
693 select * from dfunc();
694 select * from dfunc('Hello', 100);
695 select * from dfunc(a := 'Hello', c := 100);
696 select * from dfunc(c := 100, a := 'Hello');
697 select * from dfunc('Hello');
698 select * from dfunc('Hello', c := 100);
699 select * from dfunc(c := 100);
701 -- fail, can no longer change an input parameter's name
702 create or replace function dfunc(a varchar = 'def a', out _a varchar, x numeric = NULL, out _c numeric)
707 create or replace function dfunc(a varchar = 'def a', out _a varchar, numeric = NULL, out _c numeric)
712 drop function dfunc(varchar, numeric);
714 --fail, named parameters are not unique
715 create function testfoo(a int, a int) returns int as $$ select 1;$$ language sql;
716 create function testfoo(int, out a int, out a int) returns int as $$ select 1;$$ language sql;
717 create function testfoo(out a int, inout a int) returns int as $$ select 1;$$ language sql;
718 create function testfoo(a int, inout a int) returns int as $$ select 1;$$ language sql;
721 create function testfoo(a int, out a int) returns int as $$ select $1;$$ language sql;
723 drop function testfoo(int);
724 create function testfoo(a int) returns table(a int) as $$ select $1;$$ language sql;
725 select * from testfoo(37);
726 drop function testfoo(int);
728 -- test polymorphic params and defaults
729 create function dfunc(a anyelement, b anyelement = null, flag bool = true)
730 returns anyelement as $$
731 select case when $3 then $1 else $2 end;
735 select dfunc('a'::text, 'b'); -- positional notation with default
737 select dfunc(a := 1, b := 2);
738 select dfunc(a := 'a'::text, b := 'b');
739 select dfunc(a := 'a'::text, b := 'b', flag := false); -- named notation
741 select dfunc(b := 'b'::text, a := 'a'); -- named notation with default
742 select dfunc(a := 'a'::text, flag := true); -- named notation with default
743 select dfunc(a := 'a'::text, flag := false); -- named notation with default
744 select dfunc(b := 'b'::text, a := 'a', flag := true); -- named notation
746 select dfunc('a'::text, 'b', false); -- full positional notation
747 select dfunc('a'::text, 'b', flag := false); -- mixed notation
748 select dfunc('a'::text, 'b', true); -- full positional notation
749 select dfunc('a'::text, 'b', flag := true); -- mixed notation
751 -- check reverse-listing of named-arg calls
752 CREATE VIEW dfview AS
754 dfunc(q1,q2, flag := q1>q2) as c3,
755 dfunc(q1, flag := q1<q2, b := q2) as c4
758 select * from dfview;
763 drop function dfunc(anyelement, anyelement, bool);