How to Execute a Function in PostgreSQL

Formulas & functions · Updated November 22, 2026

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: psql command-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 FUNCTION or CREATE OR REPLACE FUNCTION.
  • Permissions: you need EXECUTE privilege on the function (granted by default to PUBLIC unless revoked).

Step-by-Step

  1. 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.
  2. Identify the function signature. Run \df function_name in psql or query information_schema.routines to see the exact argument types and return type. For example: SELECT routine_name, data_type FROM information_schema.routines WHERE routine_name = 'my_func';
  3. 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), use SELECT * FROM function_name(...).
  4. 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;
  5. 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).
  6. Call a procedure (not a function) with CALL. Procedures (created with CREATE PROCEDURE) are executed with CALL procedure_name(...); — they don’t return a value but can commit transactions.
  7. Use named notation (optional). For functions with many arguments, you can specify argument names: SELECT my_func(arg1 := 5, arg2 := 'text'); This improves readability.
  8. 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;
  9. 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. Use SELECT 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 SELECT but 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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.