Support use of function argument names to identify which actual arguments

match which function parameters.  The syntax uses AS, for example
	funcname(value AS arg1, anothervalue AS arg2)

Pavel Stehule

4 files changed, 469 insertions, 10 deletions

diff --git a/src/test/regress/expected/polymorphism.out b/src/test/regress/expected/polymorphism.out
index 77f693c2b14..36b31f09f43 100644
--- a/src/test/regress/expected/polymorphism.out
+++ b/src/test/regress/expected/polymorphism.out
@@ -1,5 +1,6 @@
 -- Currently this tests polymorphic aggregates and indirectly does some
 -- testing of polymorphic SQL functions.  It ought to be extended.
+-- Tests for other features related to function-calling have snuck in, too.
 -- Legend:
 -----------
 -- A = type is ANY
@@ -19,7 +20,7 @@
 -- !> = not allowed
 -- E  = exists
 -- NE = not-exists
--- 
+--
 -- Possible states:
 -- ----------------
 -- B = (A || P || N)
@@ -60,7 +61,7 @@ CREATE FUNCTION ffp(anyarray) RETURNS anyarray AS
 CREATE FUNCTION ffnp(int[]) returns int[] as
 'select $1' LANGUAGE SQL;
 -- Try to cover all the possible states:
--- 
+--
 -- Note: in Cases 1 & 2, we are trying to return P. Therefore, if the transfn
 -- is stfnp, tfnp, or tf2p, we must use ffp as finalfn, because stfnp, tfnp,
 -- and tf2p do not return P. Conversely, in Cases 3 & 4, we are trying to
@@ -837,7 +838,7 @@ select dfunc();
 
 -- verify it lists properly
 \df dfunc
-                                       List of functions
+                                           List of functions
  Schema | Name  | Result data type |                    Argument data types                    |  Type  
 --------+-------+------------------+-----------------------------------------------------------+--------
  public | dfunc | integer          | a integer DEFAULT 1, OUT sum integer, b integer DEFAULT 2 | normal
@@ -1005,7 +1006,7 @@ $$ select array_upper($1, 1) $$ language sql;
 ERROR:  cannot remove parameter defaults from existing function
 HINT:  Use DROP FUNCTION first.
 \df dfunc
-                                  List of functions
+                                      List of functions
  Schema | Name  | Result data type |               Argument data types               |  Type  
 --------+-------+------------------+-------------------------------------------------+--------
  public | dfunc | integer          | VARIADIC a integer[] DEFAULT ARRAY[]::integer[] | normal
@@ -1038,3 +1039,328 @@ select dfunc('Hi');
 drop function dfunc(int, int, int);
 drop function dfunc(int, int);
 drop function dfunc(text);
+--
+-- Tests for named- and mixed-notation function calling
+--
+create function dfunc(a int, b int, c int = 0, d int = 0)
+  returns table (a int, b int, c int, d int) as $$
+  select $1, $2, $3, $4;
+$$ language sql;
+select (dfunc(10,20,30)).*;
+ a  | b  | c  | d 
+----+----+----+---
+ 10 | 20 | 30 | 0
+(1 row)
+
+select (dfunc(10 as a, 20 as b, 30 as c)).*;
+ a  | b  | c  | d 
+----+----+----+---
+ 10 | 20 | 30 | 0
+(1 row)
+
+select * from dfunc(10 as a, 20 as b);
+ a  | b  | c | d 
+----+----+---+---
+ 10 | 20 | 0 | 0
+(1 row)
+
+select * from dfunc(10 as b, 20 as a);
+ a  | b  | c | d 
+----+----+---+---
+ 20 | 10 | 0 | 0
+(1 row)
+
+select * from dfunc(0);  -- fail
+ERROR:  function dfunc(integer) does not exist
+LINE 1: select * from dfunc(0);
+                      ^
+HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
+select * from dfunc(1,2);
+ a | b | c | d 
+---+---+---+---
+ 1 | 2 | 0 | 0
+(1 row)
+
+select * from dfunc(1,2,3 as c);
+ a | b | c | d 
+---+---+---+---
+ 1 | 2 | 3 | 0
+(1 row)
+
+select * from dfunc(1,2,3 as d);
+ a | b | c | d 
+---+---+---+---
+ 1 | 2 | 0 | 3
+(1 row)
+
+select * from dfunc(10 as x, 20 as b, 30 as x);  -- fail, duplicate name
+ERROR:  argument name "x" used more than once
+LINE 1: select * from dfunc(10 as x, 20 as b, 30 as x);
+                                                    ^
+select * from dfunc(10, 20 as b, 30);  -- fail, named args must be last
+ERROR:  positional argument cannot follow named argument
+LINE 1: select * from dfunc(10, 20 as b, 30);
+                                         ^
+select * from dfunc(10 as x, 20 as b, 30 as c);  -- fail, unknown param
+ERROR:  function dfunc(integer AS x, integer AS b, integer AS c) does not exist
+LINE 1: select * from dfunc(10 as x, 20 as b, 30 as c);
+                      ^
+HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
+select * from dfunc(10, 10, 20 as a);  -- fail, a overlaps positional parameter
+ERROR:  function dfunc(integer, integer, integer AS a) does not exist
+LINE 1: select * from dfunc(10, 10, 20 as a);
+                      ^
+HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
+select * from dfunc(1,2 as c,3 as d); -- fail, no value for b
+ERROR:  function dfunc(integer, integer AS c, integer AS d) does not exist
+LINE 1: select * from dfunc(1,2 as c,3 as d);
+                      ^
+HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
+drop function dfunc(int, int, int, int);
+-- test with different parameter types
+create function dfunc(a varchar, b numeric, c date = current_date)
+  returns table (a varchar, b numeric, c date) as $$
+  select $1, $2, $3;
+$$ language sql;
+select (dfunc('Hello World', 20, '2009-07-25'::date)).*;
+      a      | b  |     c      
+-------------+----+------------
+ Hello World | 20 | 07-25-2009
+(1 row)
+
+select * from dfunc('Hello World', 20, '2009-07-25'::date);
+      a      | b  |     c      
+-------------+----+------------
+ Hello World | 20 | 07-25-2009
+(1 row)
+
+select * from dfunc('2009-07-25'::date as c, 'Hello World' as a, 20 as b);
+      a      | b  |     c      
+-------------+----+------------
+ Hello World | 20 | 07-25-2009
+(1 row)
+
+select * from dfunc('Hello World', 20 as b, '2009-07-25'::date as c);
+      a      | b  |     c      
+-------------+----+------------
+ Hello World | 20 | 07-25-2009
+(1 row)
+
+select * from dfunc('Hello World', '2009-07-25'::date as c, 20 as b);
+      a      | b  |     c      
+-------------+----+------------
+ Hello World | 20 | 07-25-2009
+(1 row)
+
+select * from dfunc('Hello World', 20 as c, '2009-07-25'::date as b);  -- fail
+ERROR:  function dfunc(unknown, integer AS c, date AS b) does not exist
+LINE 1: select * from dfunc('Hello World', 20 as c, '2009-07-25'::da...
+                      ^
+HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
+drop function dfunc(varchar, numeric, date);
+-- test out parameters with named params
+create function dfunc(a varchar = 'def a', out _a varchar, c numeric = NULL, out _c numeric)
+returns record as $$
+  select $1, $2;
+$$ language sql;
+select (dfunc()).*;
+  _a   | _c 
+-------+----
+ def a |   
+(1 row)
+
+select * from dfunc();
+  _a   | _c 
+-------+----
+ def a |   
+(1 row)
+
+select * from dfunc('Hello', 100);
+  _a   | _c  
+-------+-----
+ Hello | 100
+(1 row)
+
+select * from dfunc('Hello' as a, 100 as c);
+  _a   | _c  
+-------+-----
+ Hello | 100
+(1 row)
+
+select * from dfunc(100 as c, 'Hello' as a);
+  _a   | _c  
+-------+-----
+ Hello | 100
+(1 row)
+
+select * from dfunc('Hello');
+  _a   | _c 
+-------+----
+ Hello |   
+(1 row)
+
+select * from dfunc('Hello', 100 as c);
+  _a   | _c  
+-------+-----
+ Hello | 100
+(1 row)
+
+select * from dfunc(100 as c);
+  _a   | _c  
+-------+-----
+ def a | 100
+(1 row)
+
+-- fail, can no longer change an input parameter's name
+create or replace function dfunc(a varchar = 'def a', out _a varchar, x numeric = NULL, out _c numeric)
+returns record as $$
+  select $1, $2;
+$$ language sql;
+ERROR:  cannot change name of input parameter "c"
+HINT:  Use DROP FUNCTION first.
+create or replace function dfunc(a varchar = 'def a', out _a varchar, numeric = NULL, out _c numeric)
+returns record as $$
+  select $1, $2;
+$$ language sql;
+ERROR:  cannot change name of input parameter "c"
+HINT:  Use DROP FUNCTION first.
+drop function dfunc(varchar, numeric);
+--fail, named parameters are not unique
+create function testfoo(a int, a int) returns int as $$ select 1;$$ language sql;
+ERROR:  parameter name "a" used more than once
+create function testfoo(int, out a int, out a int) returns int as $$ select 1;$$ language sql;
+ERROR:  parameter name "a" used more than once
+create function testfoo(out a int, inout a int) returns int as $$ select 1;$$ language sql;
+ERROR:  parameter name "a" used more than once
+create function testfoo(a int, inout a int) returns int as $$ select 1;$$ language sql;
+ERROR:  parameter name "a" used more than once
+-- valid
+create function testfoo(a int, out a int) returns int as $$ select $1;$$ language sql;
+select testfoo(37);
+ testfoo 
+---------
+      37
+(1 row)
+
+drop function testfoo(int);
+create function testfoo(a int) returns table(a int) as $$ select $1;$$ language sql;
+select * from testfoo(37);
+ a  
+----
+ 37
+(1 row)
+
+drop function testfoo(int);
+-- test polymorphic params and defaults
+create function dfunc(a anyelement, b anyelement = null, flag bool = true)
+returns anyelement as $$
+  select case when $3 then $1 else $2 end;
+$$ language sql;
+select dfunc(1,2);
+ dfunc 
+-------
+     1
+(1 row)
+
+select dfunc('a'::text, 'b'); -- positional notation with default
+ dfunc 
+-------
+ a
+(1 row)
+
+select dfunc(1 as a, 2 as b);
+ dfunc 
+-------
+     1
+(1 row)
+
+select dfunc('a'::text as a, 'b' as b);
+ dfunc 
+-------
+ a
+(1 row)
+
+select dfunc('a'::text as a, 'b' as b, false as flag); -- named notation
+ dfunc 
+-------
+ b
+(1 row)
+
+select dfunc('b'::text as b, 'a' as a); -- named notation with default
+ dfunc 
+-------
+ a
+(1 row)
+
+select dfunc('a'::text as a, true as flag); -- named notation with default
+ dfunc 
+-------
+ a
+(1 row)
+
+select dfunc('a'::text as a, false as flag); -- named notation with default
+ dfunc 
+-------
+ 
+(1 row)
+
+select dfunc('b'::text as b, 'a' as a, true as flag); -- named notation
+ dfunc 
+-------
+ a
+(1 row)
+
+select dfunc('a'::text, 'b', false); -- full positional notation
+ dfunc 
+-------
+ b
+(1 row)
+
+select dfunc('a'::text, 'b', false as flag); -- mixed notation
+ dfunc 
+-------
+ b
+(1 row)
+
+select dfunc('a'::text, 'b', true); -- full positional notation
+ dfunc 
+-------
+ a
+(1 row)
+
+select dfunc('a'::text, 'b', true as flag); -- mixed notation
+ dfunc 
+-------
+ a
+(1 row)
+
+-- check reverse-listing of named-arg calls
+CREATE VIEW dfview AS
+   SELECT q1, q2,
+     dfunc(q1,q2, q1>q2 as flag) as c3,
+     dfunc(q1, q1<q2 as flag, q2 AS b) as c4
+     FROM int8_tbl;
+select * from dfview;
+        q1        |        q2         |        c3        |        c4         
+------------------+-------------------+------------------+-------------------
+              123 |               456 |              456 |               123
+              123 |  4567890123456789 | 4567890123456789 |               123
+ 4567890123456789 |               123 | 4567890123456789 |               123
+ 4567890123456789 |  4567890123456789 | 4567890123456789 |  4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | -4567890123456789
+(5 rows)
+
+\d dfview
+    View "public.dfview"
+ Column |  Type  | Modifiers 
+--------+--------+-----------
+ q1     | bigint | 
+ q2     | bigint | 
+ c3     | bigint | 
+ c4     | bigint | 
+View definition:
+ SELECT int8_tbl.q1, int8_tbl.q2, dfunc(int8_tbl.q1, int8_tbl.q2, int8_tbl.q1 > int8_tbl.q2 AS flag) AS c3, dfunc(int8_tbl.q1, int8_tbl.q1 < int8_tbl.q2 AS flag, int8_tbl.q2 AS b) AS c4
+   FROM int8_tbl;
+
+drop view dfview;
+drop function dfunc(anyelement, anyelement, bool);
diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out
index 486dd3f3fe0..843bc53e4e7 100644
--- a/src/test/regress/expected/rangefuncs.out
+++ b/src/test/regress/expected/rangefuncs.out
@@ -159,7 +159,7 @@ SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
      1 |        1 | Joe
 (1 row)
 
-CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS 
+CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS
 (fooid int, foosubid int, fooname text);
 SELECT * FROM vw_getfoo;
  fooid | foosubid | fooname 
@@ -515,7 +515,13 @@ SELECT * FROM dup('xyz'::text);
  xyz | {xyz,xyz}
 (1 row)
 
--- equivalent specification
+-- fails, as we are attempting to rename first argument
+CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)
+AS 'select $1, array[$1,$1]' LANGUAGE sql;
+ERROR:  cannot change name of input parameter "f1"
+HINT:  Use DROP FUNCTION first.
+DROP FUNCTION dup(anyelement);
+-- equivalent behavior, though different name exposed for input arg
 CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)
 AS 'select $1, array[$1,$1]' LANGUAGE sql;
 SELECT dup(22);
diff --git a/src/test/regress/sql/polymorphism.sql b/src/test/regress/sql/polymorphism.sql
index c01871de007..2071ce63da7 100644
--- a/src/test/regress/sql/polymorphism.sql
+++ b/src/test/regress/sql/polymorphism.sql
@@ -1,5 +1,6 @@
 -- Currently this tests polymorphic aggregates and indirectly does some
 -- testing of polymorphic SQL functions.  It ought to be extended.
+-- Tests for other features related to function-calling have snuck in, too.
 
 
 -- Legend:
@@ -21,7 +22,7 @@
 -- !> = not allowed
 -- E  = exists
 -- NE = not-exists
--- 
+--
 -- Possible states:
 -- ----------------
 -- B = (A || P || N)
@@ -69,7 +70,7 @@ CREATE FUNCTION ffnp(int[]) returns int[] as
 'select $1' LANGUAGE SQL;
 
 -- Try to cover all the possible states:
--- 
+--
 -- Note: in Cases 1 & 2, we are trying to return P. Therefore, if the transfn
 -- is stfnp, tfnp, or tf2p, we must use ffp as finalfn, because stfnp, tfnp,
 -- and tf2p do not return P. Conversely, in Cases 3 & 4, we are trying to
@@ -624,3 +625,123 @@ select dfunc('Hi');
 drop function dfunc(int, int, int);
 drop function dfunc(int, int);
 drop function dfunc(text);
+
+--
+-- Tests for named- and mixed-notation function calling
+--
+
+create function dfunc(a int, b int, c int = 0, d int = 0)
+  returns table (a int, b int, c int, d int) as $$
+  select $1, $2, $3, $4;
+$$ language sql;
+
+select (dfunc(10,20,30)).*;
+select (dfunc(10 as a, 20 as b, 30 as c)).*;
+select * from dfunc(10 as a, 20 as b);
+select * from dfunc(10 as b, 20 as a);
+select * from dfunc(0);  -- fail
+select * from dfunc(1,2);
+select * from dfunc(1,2,3 as c);
+select * from dfunc(1,2,3 as d);
+
+select * from dfunc(10 as x, 20 as b, 30 as x);  -- fail, duplicate name
+select * from dfunc(10, 20 as b, 30);  -- fail, named args must be last
+select * from dfunc(10 as x, 20 as b, 30 as c);  -- fail, unknown param
+select * from dfunc(10, 10, 20 as a);  -- fail, a overlaps positional parameter
+select * from dfunc(1,2 as c,3 as d); -- fail, no value for b
+
+drop function dfunc(int, int, int, int);
+
+-- test with different parameter types
+create function dfunc(a varchar, b numeric, c date = current_date)
+  returns table (a varchar, b numeric, c date) as $$
+  select $1, $2, $3;
+$$ language sql;
+
+select (dfunc('Hello World', 20, '2009-07-25'::date)).*;
+select * from dfunc('Hello World', 20, '2009-07-25'::date);
+select * from dfunc('2009-07-25'::date as c, 'Hello World' as a, 20 as b);
+select * from dfunc('Hello World', 20 as b, '2009-07-25'::date as c);
+select * from dfunc('Hello World', '2009-07-25'::date as c, 20 as b);
+select * from dfunc('Hello World', 20 as c, '2009-07-25'::date as b);  -- fail
+
+drop function dfunc(varchar, numeric, date);
+
+-- test out parameters with named params
+create function dfunc(a varchar = 'def a', out _a varchar, c numeric = NULL, out _c numeric)
+returns record as $$
+  select $1, $2;
+$$ language sql;
+
+select (dfunc()).*;
+select * from dfunc();
+select * from dfunc('Hello', 100);
+select * from dfunc('Hello' as a, 100 as c);
+select * from dfunc(100 as c, 'Hello' as a);
+select * from dfunc('Hello');
+select * from dfunc('Hello', 100 as c);
+select * from dfunc(100 as c);
+
+-- fail, can no longer change an input parameter's name
+create or replace function dfunc(a varchar = 'def a', out _a varchar, x numeric = NULL, out _c numeric)
+returns record as $$
+  select $1, $2;
+$$ language sql;
+
+create or replace function dfunc(a varchar = 'def a', out _a varchar, numeric = NULL, out _c numeric)
+returns record as $$
+  select $1, $2;
+$$ language sql;
+
+drop function dfunc(varchar, numeric);
+
+--fail, named parameters are not unique
+create function testfoo(a int, a int) returns int as $$ select 1;$$ language sql;
+create function testfoo(int, out a int, out a int) returns int as $$ select 1;$$ language sql;
+create function testfoo(out a int, inout a int) returns int as $$ select 1;$$ language sql;
+create function testfoo(a int, inout a int) returns int as $$ select 1;$$ language sql;
+
+-- valid
+create function testfoo(a int, out a int) returns int as $$ select $1;$$ language sql;
+select testfoo(37);
+drop function testfoo(int);
+create function testfoo(a int) returns table(a int) as $$ select $1;$$ language sql;
+select * from testfoo(37);
+drop function testfoo(int);
+
+-- test polymorphic params and defaults
+create function dfunc(a anyelement, b anyelement = null, flag bool = true)
+returns anyelement as $$
+  select case when $3 then $1 else $2 end;
+$$ language sql;
+
+select dfunc(1,2);
+select dfunc('a'::text, 'b'); -- positional notation with default
+
+select dfunc(1 as a, 2 as b);
+select dfunc('a'::text as a, 'b' as b);
+select dfunc('a'::text as a, 'b' as b, false as flag); -- named notation
+
+select dfunc('b'::text as b, 'a' as a); -- named notation with default
+select dfunc('a'::text as a, true as flag); -- named notation with default
+select dfunc('a'::text as a, false as flag); -- named notation with default
+select dfunc('b'::text as b, 'a' as a, true as flag); -- named notation
+
+select dfunc('a'::text, 'b', false); -- full positional notation
+select dfunc('a'::text, 'b', false as flag); -- mixed notation
+select dfunc('a'::text, 'b', true); -- full positional notation
+select dfunc('a'::text, 'b', true as flag); -- mixed notation
+
+-- check reverse-listing of named-arg calls
+CREATE VIEW dfview AS
+   SELECT q1, q2,
+     dfunc(q1,q2, q1>q2 as flag) as c3,
+     dfunc(q1, q1<q2 as flag, q2 AS b) as c4
+     FROM int8_tbl;
+
+select * from dfview;
+
+\d dfview
+
+drop view dfview;
+drop function dfunc(anyelement, anyelement, bool);
diff --git a/src/test/regress/sql/rangefuncs.sql b/src/test/regress/sql/rangefuncs.sql
index 3727a36aaff..172bbc73a9e 100644
--- a/src/test/regress/sql/rangefuncs.sql
+++ b/src/test/regress/sql/rangefuncs.sql
@@ -70,7 +70,7 @@ DROP VIEW vw_getfoo;
 DROP FUNCTION getfoo(int);
 CREATE FUNCTION getfoo(int) RETURNS RECORD AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
 SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
-CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS 
+CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS
 (fooid int, foosubid int, fooname text);
 SELECT * FROM vw_getfoo;
 
@@ -251,7 +251,13 @@ SELECT dup('xyz');	-- fails
 SELECT dup('xyz'::text);
 SELECT * FROM dup('xyz'::text);
 
--- equivalent specification
+-- fails, as we are attempting to rename first argument
+CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)
+AS 'select $1, array[$1,$1]' LANGUAGE sql;
+
+DROP FUNCTION dup(anyelement);
+
+-- equivalent behavior, though different name exposed for input arg
 CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)
 AS 'select $1, array[$1,$1]' LANGUAGE sql;
 SELECT dup(22);