Calling Functions

We’ll use the following function in the examples below:

mydb=> CREATE FUNCTION due_for_purchase(top_n int, threshold int DEFAULT 24)
mydb->   RETURNS TABLE (name text, num_items_left int) AS '
mydb'>     SELECT name, items_in_stock
mydb'>       FROM products
mydb'>       WHERE items_in_stock < $2
mydb'>       ORDER BY items_in_stock LIMIT $1
mydb'>     '
mydb->   LANGUAGE SQL IMMUTABLE STRICT;
CREATE FUNCTION

1. Positional Notation

Arguments are supplied in the same order as defined in the function declaration:

mydb=> SELECT * FROM due_for_purchase(10, 100);  -- get at most 10 products with < 100 items left
     name     | num_items_left 
--------------+----------------
 Pumpkins     |             10
 Spinach      |             19
 Watermelons  |             22
 Pomegranates |             25
 Bananas      |             32
 Mangoes      |             38
 Tomatoes     |             43
 Lemons       |             49
 Kiwis        |             54
 Pineapples   |             56
(10 rows)

Optional arguments (those with default values) can only be omitted from right to left:

mydb=> SELECT * FROM due_for_purchase(10);  -- using default threshold (24)
    name     | num_items_left 
-------------+----------------
 Pumpkins    |             10
 Spinach     |             19
 Watermelons |             22
(3 rows)

2. Named Notation

Arguments are supplied as arg_name => arg_value, and in any order:

mydb=> SELECT * FROM due_for_purchase(threshold => 50, top_n => 5);
     name     | num_items_left 
--------------+----------------
 Pumpkins     |             10
 Spinach      |             19
 Watermelons  |             22
 Pomegranates |             25
 Bananas      |             32
(5 rows)

Optional arguments can be omitted in any order.

An older syntax based on := is supported for backward compatibility:

mydb=> SELECT * FROM due_for_purchase(top_n := 2);  -- using default threshold (24)
   name   | num_items_left 
----------+----------------
 Pumpkins |             10
 Spinach  |             19
(2 rows)

3. Mixed Notation

Combines positional and named notation. Named arguments cannot precede positional ones.

mydb=> SELECT * FROM due_for_purchase(5, threshold => 60);
     name     | num_items_left 
--------------+----------------
 Pumpkins     |             10
 Spinach      |             19
 Watermelons  |             22
 Pomegranates |             25
 Bananas      |             32
(5 rows)