Executing a function in PostgreSQL means calling it so it runs its SQL logic and returns a result. You can call a function with a simple SELECT statement, use it inside a query, or invoke it with PERFORM in PL/pgSQL code when you don’t need the return value. Before you begin, you need a PostgreSQL database with the function already created, and you need the appropriate permissions (usually the function’s owner or a superuser). This guide covers the main ways to execute a function, with examples and common pitfalls.
What You’ll Need
- A running PostgreSQL server (local or remote) with a database you can connect to.
- A client tool:
psqlcommand-line, pgAdmin, DBeaver, or any SQL editor. - The function you want to execute must already exist in the database. If not, create it first with
CREATE FUNCTIONorCREATE OR REPLACE FUNCTION. - Permissions: you need EXECUTE privilege on the function (granted by default to PUBLIC unless revoked).
Step-by-Step
- Connect to your database. Use
psql:psql -U username -d dbname, or open a query tool in pgAdmin. You’ll be prompted for a password if required. - Identify the function signature. Run
\df function_namein psql or queryinformation_schema.routinesto see the exact argument types and return type. For example:SELECT routine_name, data_type FROM information_schema.routines WHERE routine_name = 'my_func'; - Execute with SELECT for a scalar or set-returning function. The most common way is
SELECT function_name(arg1, arg2);. For a function that returns a single value, this shows the result. For a function returning a table (SETOF), useSELECT * FROM function_name(...). - Use the function inside a larger query. You can call it in the SELECT list, WHERE clause, or JOIN condition. Example:
SELECT id, my_func(id) AS computed FROM my_table; - Execute with PERFORM in PL/pgSQL. If you are writing a function or DO block and don’t need the result, use
PERFORM function_name(...);instead of SELECT (which would complain about unused result). - Call a procedure (not a function) with CALL. Procedures (created with
CREATE PROCEDURE) are executed withCALL procedure_name(...);— they don’t return a value but can commit transactions. - Use named notation (optional). For functions with many arguments, you can specify argument names:
SELECT my_func(arg1 := 5, arg2 := 'text');This improves readability. - Handle function that returns a table. If the function returns a set of rows, use
SELECT * FROM func(...)to get columns. You can also join it:SELECT t.*, f.extra FROM my_table t CROSS JOIN LATERAL func(t.id) AS f; - Execute a function in a trigger context. Trigger functions are executed automatically when the trigger fires; you don’t call them manually. They are created with
CREATE TRIGGER ... EXECUTE FUNCTION my_trigger_func().
Common Mistakes
- Using SELECT for a procedure. Procedures must be called with CALL, not SELECT. SELECT will throw an error.
- Wrong argument types. PostgreSQL is strict about types. If you pass an integer where a text is expected, it may fail or implicitly cast, causing confusion. Check the function signature.
- Forgetting the parentheses for zero-argument functions. Even with no arguments, you must write
SELECT my_func();— omitting the parentheses is a syntax error. - Using SELECT * from a scalar function. If the function returns a single value,
SELECT * FROM my_func()will fail because it expects a table. UseSELECT my_func();instead. - Not schema-qualifying the function name. If the function is in a schema other than the search_path, you get a ‘function does not exist’ error. Use
SELECT schema_name.func();or set search_path.
Tips
- Use \df+ in psql to see the full function definition and source code, which helps understand expected arguments.
- Test with a dummy call. If unsure about output, run
SELECT my_func(1);with sample values before integrating into complex queries. - Use dollar-quoting when creating functions to avoid escaping single quotes in the body, but that’s for creation, not execution.
- For performance, avoid calling functions in WHERE clauses on large tables because it can prevent index usage. Instead, precompute or use a generated column.
- Check the return type. If the function returns a composite type, you may need to expand it with
(func()).*or select specific fields.
Troubleshooting
- Error: function does not exist. Check spelling, schema, and argument types. Run
\df *name*to list matching functions. Also verify the search_path:SHOW search_path;and include the schema if needed. - Error: function returns a set, but SELECT only accepts one. If the function returns SETOF, you must use
SELECT * FROM func()or put it in the FROM clause, not in the SELECT list. - Error: column reference is ambiguous. When joining a function that returns a table with other tables, prefix columns with the function alias or table alias.
- Error: permission denied. The function may have EXECUTE revoked. Grant it with
GRANT EXECUTE ON FUNCTION func TO username;or run as the owner. - Function runs but returns no rows. If it’s a SELECT, check if the function’s logic is filtering everything out. Test with simpler inputs.
- Transaction control not allowed in function. If your function tries to COMMIT or ROLLBACK, it will fail because functions run inside a transaction. Use a procedure instead.
Frequently Asked Questions
- What is the difference between a function and a procedure in PostgreSQL? A function returns a value and can be used in SELECT; a procedure does not return a value and is called with CALL. Procedures can manage transactions, functions cannot.
- Can I execute a function without using SELECT? Yes, in PL/pgSQL you can use PERFORM. In SQL, you can also use
SELECTbut ignore the result; however, PERFORM is cleaner inside procedural code. - How do I execute a function that returns multiple rows? Use
SELECT * FROM function_name(...)to get all rows as a result set. - Can I call a function from another function? Yes, simply use
SELECT function_name(...)inside the body of the outer function, or PERFORM if you don’t need the result. - Why does my function execute but return nothing? Check if the function is declared as RETURNS void — then it intentionally returns nothing. Also check for conditional logic that might skip the RETURN statement.
- How do I execute a function with a table name as argument? You cannot pass a table name directly as a string unless the function uses dynamic SQL (EXECUTE). The function must be written to accept a text parameter and internally build the query.
Related
- Print String and Int on Arduino
- Arduino String Data Type: How It Works and When to Use It
- Snowflake SPLIT: Turn a String Into an Array
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel INDIRECT Function: How to Use It with Cell ReferencesTo make Excel interpret text as a cell reference, use INDIRECT. With “B3” in A1 and 42 in…
- Excel Roundup a Sum: 10 Useful Formula PatternsTo round an Excel total up to the next whole number, put the amounts in B2:B5 and enter…
- Excel AVERAGEIF vs AVERAGEIFS: Rules and ExamplesUse AVERAGEIF when you need to average values that meet one condition; use AVERAGEIFS when every result must…
- Divide Symbol on Keyboard in Excel: Use / for FormulasIn Excel, the divide key for calculations is the forward slash (/): enter a formula such as =A1/B1…
- Weighted Average in Excel: Formulas and ExamplesYes. In Excel, calculate a weighted average by multiplying each value by its weight, adding those products, and…
- Link Excel Data Validation to a Table the Right WayYes. In Excel for Microsoft 365 on Windows, the reliable way to link a dropdown list to a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.