To call a function in PostgreSQL, you use the SELECT statement with the function name and its arguments, like SELECT function_name(arg1, arg2);. For procedures (created with CREATE PROCEDURE), you use CALL procedure_name(args); instead. This article explains both methods, their differences, and common pitfalls.
Background: Functions vs. Procedures
PostgreSQL distinguishes between functions (which return a value and are called with SELECT) and procedures (which may not return a value and are called with CALL). Functions can be used in queries, expressions, and even as part of a WHERE clause. Procedures, introduced in PostgreSQL 11, support transaction control (like COMMIT and ROLLBACK) inside their body, which functions cannot. If you created a function with CREATE FUNCTION, call it with SELECT; if you created a procedure with CREATE PROCEDURE, call it with CALL.
Basic Function Call Syntax
The simplest way to call a function is:
SELECT function_name(argument1, argument2);
For example, if you have a function that adds two integers:
CREATE FUNCTION add_numbers(a integer, b integer) RETURNS integer AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql;
You call it with:
SELECT add_numbers(5, 3);
This returns 8. You can also use SELECT * FROM function_name(...) if the function returns a set (a table). For a function that returns a single value, the SELECT without FROM works fine.
Calling Functions with Named Arguments
PostgreSQL allows you to call functions using named notation, which improves readability and avoids order mistakes. The syntax is:
SELECT add_numbers(a := 5, b := 3);
You can also mix positional and named arguments, but once you use a named argument, all subsequent arguments must be named as well. For example, if a function has default values, you can omit some arguments:
CREATE FUNCTION greet(name text, greeting text DEFAULT 'Hello') RETURNS text AS $$
BEGIN
RETURN greeting || ', ' || name;
END;
$$ LANGUAGE plpgsql;
Call with only the name:
SELECT greet('Alice');
Returns Hello, Alice. To override the default, use named notation:
SELECT greet(name := 'Bob', greeting := 'Hi');
Calling Procedures with CALL
Procedures are called with the CALL keyword, not SELECT. For example:
CREATE PROCEDURE update_salary(emp_id integer, new_salary numeric) AS $$
BEGIN
UPDATE employees SET salary = new_salary WHERE id = emp_id;
END;
$$ LANGUAGE plpgsql;
Call it with:
CALL update_salary(101, 75000);
Procedures can also have output parameters, which you can capture with CALL using a variable in a DO block or from a client. In plain SQL, CALL does not return a result set; it just executes the procedure. If you need to get a value back from a procedure, you can use an output parameter and call it in a DO block or from a programming language like Python or Java.
Using Functions in Queries and Expressions
One of the strengths of PostgreSQL functions is that they can be used anywhere an expression is allowed. For instance, in a WHERE clause:
SELECT * FROM orders WHERE total_amount > calculate_tax(total_amount);
Or in a SELECT list:
SELECT order_id, apply_discount(order_total, 0.1) AS discounted FROM orders;
If a function returns a set (using RETURNS SETOF or RETURNS TABLE), you can call it in the FROM clause like a table:
SELECT * FROM get_users_by_role('admin');
This is common for functions that return multiple rows.
Common Pitfalls and Misconceptions
A frequent mistake is trying to call a function with CALL when it was created with CREATE FUNCTION. This results in an error like function ... does not exist because CALL is only for procedures. Conversely, using SELECT on a procedure also fails. Another issue is forgetting to include parentheses when the function has no arguments: you must write SELECT now(); not SELECT now; (though now is a special case that works without parentheses in some contexts, but generally functions require parentheses). Also, be careful with function overloading: if you have two functions with the same name but different argument types, PostgreSQL chooses the one that matches the provided arguments. If ambiguous, you may need to cast arguments explicitly, e.g., SELECT my_func(1::text);.
Performance Considerations
Calling a function in a SELECT statement can impact performance if the function is called for each row in a large query. For example, SELECT expensive_function(col) FROM big_table; runs the function once per row. To avoid this, consider using a LATERAL join or rewriting the logic as a single SQL statement. Also, functions declared as STABLE or IMMUTABLE can be optimized by PostgreSQL (e.g., evaluated once if arguments are constant), while VOLATILE functions are always re-evaluated. Declaring the correct volatility helps the planner. For functions that are called repeatedly, consider using SQL language functions instead of PL/pgSQL if they are simple, as they may be inlined.
Calling from Client Applications
When you call a function from a client (e.g., Python’s psycopg2, Java JDBC, or psql), the same SQL syntax applies. In psql, you can just type the SELECT or CALL statement. In a programming language, you typically use a prepared statement. For example, in Python:
import psycopg2
conn = psycopg2.connect("dbname=test")
cur = conn.cursor()
cur.execute("SELECT add_numbers(%s, %s)", (5, 3))
result = cur.fetchone()[0]
print(result) # 8
For procedures, you still use CALL in the SQL string. Some drivers also support calling functions directly via a callable statement, but the underlying SQL is the same.
Frequently Asked Questions
Can I call a function without parentheses?
No, you must use parentheses even if the function has no arguments. For example, SELECT version(); not SELECT version;. There are a few built-in functions like now that are special and can be used without parentheses, but for user-defined functions, parentheses are required.
What is the difference between SELECT and CALL?
SELECT is used to call functions (which return a value) and can be used in queries. CALL is used to invoke procedures (which may not return a value) and is a standalone statement. Procedures can manage transactions, functions cannot.
How do I call a function that returns a table?
Use SELECT * FROM function_name(args); or SELECT column_list FROM function_name(args);. This works for functions defined with RETURNS TABLE or RETURNS SETOF.
Can I call a function inside another function?
Yes, you can call any function from within a PL/pgSQL function or SQL function. Just use the same SELECT syntax, or assign the result to a variable. For example, in PL/pgSQL: result := add_numbers(5, 3);.
Why do I get an error saying the function does not exist?
This often happens when you use CALL on a function or SELECT on a procedure. Check the function type. Also, verify the schema search path: if the function is in a non-default schema, you may need to qualify it with the schema name, e.g., SELECT myschema.my_func();.
Can I call a function with a table name as an argument?
Yes, but you must pass the table name as a text value, not as an identifier. For example, if you have a function that takes a table name, call it with a string literal: SELECT count_rows('users');. Inside the function, you would use dynamic SQL to execute a query against that table.
Related
- Arduino String Data Type: How It Works and When to Use It
- Snowflake SPLIT: Turn a String Into an Array
- C++ String Data Type: How std::string Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Time Conversion Formula: Hours, Minutes, and MoreIf A2 contains an Excel time, multiply it by 24 to get decimal hours: =A2*24. For decimal minutes,…
- Excel Formula for Minus Amounts: Examples and TipsTo subtract one amount from another in Excel, enter a formula with the minus sign: if the first…
- How to Delete a Whole Column in Excel the Right WayTo delete a whole column in Excel, select its lettered header, then choose Home > Delete > Delete…
- Excel Formula for Age From Date of BirthThe simplest Excel formula for age from a date of birth is =DATEDIF(A2,TODAY(),"Y"), assuming the birth date is…
- DMAX Formula in Excel: Syntax, Examples, and CriteriaExcel’s DMAX function returns the largest numeric value in a database field among records that meet specified criteria.…
- Percentage Change Calculator Formula in Excel: ExamplesTo calculate percentage change in Excel, subtract the old value from the new value, then divide by the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.