SQL Functions with Variable Numbers of Arguments

Question

CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$
    SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;

SELECT mleast(10, -1, 5, 4.4);

link: https://www.postgresql.org/docs/9.5/xfunc-sql.html Please kindly check: 35.4.5. SQL Functions with Variable Numbers of Arguments The part I understand is:

• Example like FROM generate_series(2,4)
• min function.

What i don't understand:

  min($1[i])
     g(i)

Answer 1

It easier to understand when running in steps and passing actual value:

-- 1.generate subscripts from 1 to n(where n is number of elements)
SELECT *
FROM generate_subscripts('{10, -1, 5, 4.4}'::numeric[], 1) g(i); 

+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+

-- 2. get actual value at specific subscript
SELECT  ('{10, -1, 5, 4.4}'::numeric[])[i]
FROM generate_subscripts('{10, -1, 5, 4.4}'::numeric[], 1) g(i);

+---------+
| numeric |
+---------+
|      10 |
|      -1 |
|       5 |
|     4.4 |
+---------+

-- 3. find minimum value from entire set
SELECT  MIN(('{10, -1, 5, 4.4}'::numeric[])[i])
FROM generate_subscripts('{10, -1, 5, 4.4}'::numeric[], 1) g(i);

+-----+
| min |
+-----+
|  -1 |
+-----+

db<>fiddle demo

g(i) - is an alias for result set from function call and returned value

min($1[i]) - aggregate min function over array provided as first parameter with subscript i

---

SELECT * -- g.i
FROM generate_subscripts('{10, -1, 5, 4.4}'::numeric[], 1) g(i); 
+---+
| i | -- "i" as the column alias
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
SELECT *
FROM generate_subscripts('{10, -1, 5, 4.4}'::numeric[], 1); 
+---------------------+
| generate_subscripts |
+---------------------+
|                   1 |
|                   2 |
|                   3 |
|                   4 |
+---------------------+