Skip to content
SQLViews, Procedures, and Securityadvanced26 min read

Stored Procedures and Functions

Learn to create reusable PostgreSQL functions and stored procedures using PL/pgSQL, including parameters, return values, control flow, error handling, and transaction management.

On This Page (Table of Contents)

In this lesson, you will learn

  • ✓Explain the differences between PostgreSQL functions and stored procedures
  • ✓Create functions using SQL and PL/pgSQL
  • ✓Define input parameters and return scalar values or result sets
  • ✓Use variables, conditional statements, loops, and exception handling
  • ✓Create and execute stored procedures with CALL
  • ✓Understand transaction control, security considerations, and practical use cases

1. Introduction to Stored Procedures and Functions

A database routine is reusable logic stored inside a database. Instead of repeating the same SQL statements in multiple applications, you can define a routine once and invoke it whenever needed.

PostgreSQL provides two important kinds of routines:

**Functions:** A function returns a value or a set of rows. You can call it in SQL expressions, SELECT statements, and other queries when its return type and behavior allow it.

**Stored procedures:** A procedure is invoked with the CALL statement. It is useful for performing database operations as a named unit of work. Eligible procedures can also control transactions.

For example, an application might need to calculate an order's total repeatedly. A function can centralize that calculation. A procedure might process a pending order by updating its status and recording a processing event.

Both functions and procedures can contain SQL statements. PL/pgSQL adds procedural features such as variables, IF statements, loops, and exception handling.

The examples in this lesson use PostgreSQL syntax. Some features, including the security_invoker option for views discussed in the previous lesson, depend on the PostgreSQL version. Function and procedure behavior should likewise be checked against the version used by your project.

2. Functions vs. Stored Procedures

The main difference is how each routine is called, what it returns, and whether it can control transactions.

| Feature | Function | Procedure |
|---|---|---|
| Invocation | SELECT or another permitted SQL expression | CALL |
| Return behavior | Returns its declared result, which can be a scalar or a set of rows | Does not use a function-style RETURN value; can use output parameters |
| Transaction control | Cannot commit or roll back the surrounding transaction | Can control transactions in eligible execution contexts |
| Typical purpose | Calculations, reusable query logic, returning data | Multi-step database operations and workflows |
| Parameters | IN, OUT, INOUT, and other supported function parameter modes | IN, OUT, INOUT, subject to PostgreSQL version and call syntax |
| Definition | CREATE FUNCTION | CREATE PROCEDURE |

A function does not have to be read-only. It can perform database changes if its language and declared behavior permit them. However, a function cannot execute transaction-control statements such as COMMIT or ROLLBACK.

A procedure is not automatically a better choice for every operation that changes data. Choose based on whether you need a function result, transaction control, or a named database workflow.

**Remember:** Functions are called as expressions or queries; procedures are called with CALL.

3. Creating Your First Function

PostgreSQL functions are created with CREATE FUNCTION.

A basic function definition contains:
- A function name.
- Optional parameters.
- A RETURNS declaration.
- A language declaration.
- A function body.

PL/pgSQL function bodies commonly use dollar quoting, such as $$ ... $$. This avoids having to escape every single quote in the body.

The following function accepts two numbers and returns their sum.

Create and call a simple function

Create and call a simple function
sql
CREATE OR REPLACE FUNCTION add_numbers(
    a INTEGER,
    b INTEGER
)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a + b;
END;
$$;
 
SELECT add_numbers(10, 20) AS result;
Output
result -------- 30 (1 row)

The function accepts two integer arguments, calculates their sum, and returns an integer. SELECT invokes the function and displays its returned value.

4. Understanding the PL/pgSQL Function Structure

PL/pgSQL is PostgreSQL's procedural language. It lets you combine ordinary SQL with programming constructs.

A typical PL/pgSQL block looks like this:

DECLARE
-- Optional variable declarations
BEGIN
-- Required executable statements
EXCEPTION
-- Optional error-handling section
END;

DECLARE is optional and is used to define local variables. BEGIN starts the executable section. EXCEPTION is optional and handles selected errors. END closes the block.

The BEGIN and END keywords here delimit a PL/pgSQL block. They do not start and finish a database transaction.

A function declared with RETURNS INTEGER must return an integer value on every normal execution path. A function declared with RETURNS VOID does not return a useful scalar result, although it can still perform operations.

Use descriptive names for variables and parameters. When a name could refer to both a column and a variable, qualify column references with a table alias to avoid ambiguity.

5. Using Parameters and Variables

Parameters allow a routine to receive values from its caller. Variables hold temporary values while the routine executes.

In PL/pgSQL, parameters can be referenced by their declared names. Local variables are declared in the DECLARE section and can be assigned values using :=.

The example below calculates a discounted price. The discount percentage is supplied as a parameter, and the calculated result is stored in a local variable.

Function with parameters and a local variable

Function with parameters and a local variable
sql
CREATE OR REPLACE FUNCTION discounted_price(
    original_price NUMERIC,
    discount_percent NUMERIC
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    final_price NUMERIC;
BEGIN
    final_price :=
        original_price * (1 - discount_percent / 100);
 
    RETURN final_price;
END;
$$;
 
SELECT discounted_price(1000, 10) AS final_price;
Output
final_price ------------- 900.0 (1 row)

The function calculates a 10% discount on 1000 and returns 900. NUMERIC is suitable for many decimal calculations involving prices, although applications should also define appropriate precision and scale.

6. Returning Data from Functions

PostgreSQL functions can return different kinds of results.

**Scalar return:** RETURNS INTEGER, RETURNS TEXT, and RETURNS NUMERIC return a single value.

**Void return:** RETURNS VOID indicates that the function does not return a useful scalar result.

**Set return:** RETURNS SETOF table_type or RETURNS TABLE(...) allows a function to return multiple rows.

For example, a reporting function might return all employees whose salary exceeds a supplied minimum. A table-returning function can be queried similarly to a table.

A function that returns rows should define the result columns clearly. Callers can then filter, sort, or join the returned rows as appropriate.

When a function returns sensitive records, remember that returning data through a routine does not automatically make that data safe. Permissions and access-control behavior must be designed deliberately.

7. Returning Multiple Rows with RETURNS TABLE

Use RETURNS TABLE when you want to define the names and types of the columns returned by a function.

In the example, the function returns products priced above a supplied minimum. The RETURNS TABLE declaration defines the shape of the result, while RETURN QUERY executes a query and appends its rows to the function result.

The table is created and populated here so the example can be run independently.

Return a query result from a function

Return a query result from a function
sql
CREATE TEMP TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);
 
INSERT INTO products (product_id, product_name, price)
VALUES
    (1, 'Keyboard', 800.00),
    (2, 'Monitor', 12000.00),
    (3, 'Mouse', 500.00);
 
CREATE OR REPLACE FUNCTION get_products_above(
    minimum_price NUMERIC
)
RETURNS TABLE (
    product_id INTEGER,
    product_name TEXT,
    price NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT p.product_id, p.product_name, p.price
    FROM products AS p
    WHERE p.price > minimum_price
    ORDER BY p.price;
END;
$$;
 
SELECT *
FROM get_products_above(700);
Output
product_id | product_name | price ------------+--------------+--------- 1 | Keyboard | 800.00 2 | Monitor | 12000.00 (2 rows)

RETURN QUERY appends the rows produced by the SELECT statement to the function's result. The strict greater-than condition excludes the Mouse because its price is 500.

8. SQL Functions and PL/pgSQL Functions

PostgreSQL supports functions written in different languages, including SQL and PL/pgSQL.

An SQL-language function is useful when the implementation can be expressed as a SQL query. A PL/pgSQL function is useful when you need variables, conditional branches, loops, or exception handling.

For a simple calculation or query, an SQL function may be shorter and easier to read. For multi-step procedural logic, PL/pgSQL often provides a clearer structure.

Example of an SQL-language function:

CREATE OR REPLACE FUNCTION square_number(n INTEGER)
RETURNS INTEGER
LANGUAGE SQL
AS $$
SELECT n * n;
$$;

SELECT square_number(5);

Expected result: 25.

Do not choose PL/pgSQL automatically just because the routine is stored in the database. Prefer the simplest implementation that expresses the required behavior correctly.

9. Conditional Logic with IF and ELSIF

PL/pgSQL supports IF, ELSIF, ELSE, and END IF.

Use conditional logic when a routine must make a decision based on its input or a value retrieved from the database.

This function classifies a numeric score. It deliberately handles NULL separately because comparisons involving NULL do not evaluate to TRUE or FALSE in the usual way; they evaluate to unknown.

Use IF and ELSIF in a function

Use IF and ELSIF in a function
sql
CREATE OR REPLACE FUNCTION classify_score(score INTEGER)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
    IF score IS NULL THEN
        RETURN 'Unknown';
    ELSIF score >= 80 THEN
        RETURN 'Excellent';
    ELSIF score >= 50 THEN
        RETURN 'Pass';
    ELSE
        RETURN 'Needs improvement';
    END IF;
END;
$$;
 
SELECT
    classify_score(90) AS high_score,
    classify_score(65) AS passing_score,
    classify_score(30) AS low_score,
    classify_score(NULL) AS missing_score;
Output
high_score | passing_score | low_score | missing_score ------------+---------------+--------------------+--------------- Excellent | Pass | Needs improvement | Unknown (1 row)

The function checks conditions in order. Once a condition matches, its RETURN statement ends that function invocation. The explicit NULL check prevents missing data from being incorrectly classified as a low score.

10. Loops and Repeated Operations

PL/pgSQL supports LOOP, WHILE, and FOR constructs.

Loops are useful when the task genuinely requires procedural repetition. However, do not replace a set-based SQL operation with a loop without a reason. A single UPDATE or INSERT ... SELECT is often simpler and more efficient than processing many rows one at a time.

For example, this block prints the numbers from 1 through 3:

DO $$
DECLARE
counter INTEGER := 1;
BEGIN
WHILE counter <= 3 LOOP
RAISE NOTICE 'Counter: %', counter;
counter := counter + 1;
END LOOP;
END;
$$;

The client displays three NOTICE messages. Their exact presentation depends on the SQL client.

DO executes an anonymous code block and does not create a reusable function or procedure. Use CREATE FUNCTION or CREATE PROCEDURE when the logic needs a persistent named routine.

11. Creating and Calling Stored Procedures

Create a procedure with CREATE PROCEDURE and execute it using CALL.

Unlike a function, a procedure does not use a RETURNS clause for a function-style return value. It can nevertheless perform operations such as inserting or updating records.

The following example creates a table and a procedure that records a payment. The procedure checks that the amount is positive before inserting it.

A production payment workflow would usually require additional validation, authorization, idempotency, and integration with the application's transaction design.

Create and execute a procedure

Create and execute a procedure
sql
CREATE TEMP TABLE payments (
    payment_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    amount NUMERIC(10, 2) NOT NULL CHECK (amount > 0)
);
 
CREATE OR REPLACE PROCEDURE record_payment(
    p_customer_id INTEGER,
    p_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF p_customer_id IS NULL OR p_amount IS NULL THEN
        RAISE EXCEPTION 'Customer ID and amount are required';
    END IF;
 
    IF p_amount <= 0 THEN
        RAISE EXCEPTION 'Payment amount must be positive';
    END IF;
 
    INSERT INTO payments (customer_id, amount)
    VALUES (p_customer_id, p_amount);
END;
$$;
 
CALL record_payment(101, 250.00);
 
SELECT customer_id, amount
FROM payments;
Output
customer_id | amount -------------+-------- 101 | 250.00 (1 row)

CALL executes the procedure, which validates the inputs and inserts a payment. The SELECT statement verifies the inserted row. The temporary table exists only for the current database session.

12. Transaction Management in Procedures

Transactions group database changes into a unit of work. COMMIT makes a transaction's changes permanent, while ROLLBACK discards its uncommitted changes.

PostgreSQL procedures can execute transaction-control statements in eligible contexts. Important restrictions apply:

- A procedure called inside an explicit transaction block cannot independently commit or roll back that transaction.
- Transaction control is available only in permitted CALL or DO invocation contexts.
- A procedure cannot freely perform transaction control in every nested execution context.
- Ordinary functions cannot execute COMMIT or ROLLBACK.

For example, a procedure invoked with a top-level CALL, outside an explicit BEGIN ... COMMIT transaction block, may commit its work when the execution context and procedure definition permit it.

Do not add COMMIT after every SQL statement by default. Decide transaction boundaries based on the business operation and the consistency guarantees required by the application.

When the application manages a transaction, keep transaction ownership clear. Unexpected commits inside a database workflow can make rollback and error recovery more difficult.

13. Exception Handling and Raising Errors

PL/pgSQL uses RAISE to report messages or raise errors. An EXCEPTION block can catch selected errors and handle them.

The example below demonstrates handling division by zero. It is intended to teach the exception mechanism rather than encourage using exceptions for routine control flow.

When an exception is caught, the statements in the relevant protected block are rolled back to the block's implicit savepoint. The handler can then continue execution or raise the error again, depending on the design.

Handle an exception in PL/pgSQL

Handle an exception in PL/pgSQL
sql
CREATE OR REPLACE FUNCTION safe_divide(
    numerator NUMERIC,
    denominator NUMERIC
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    IF numerator IS NULL OR denominator IS NULL THEN
        RAISE EXCEPTION 'Arguments cannot be NULL';
    END IF;
 
    IF denominator = 0 THEN
        RAISE EXCEPTION 'Denominator cannot be zero';
    END IF;
 
    RETURN numerator / denominator;
 
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Division by zero was detected';
        RETURN NULL;
END;
$$;
 
SELECT safe_divide(10, 2) AS normal_result;
SELECT safe_divide(10, 0) AS zero_result;
Output
normal_result --------------- 5 (1 row) NOTICE: Division by zero was detected zero_result ------------- NULL (1 row)

The explicit denominator check raises a custom exception before division occurs, so the division_by_zero handler is not reached for that input. This example's handler illustrates the built-in condition, but the custom validation exception is intentionally not swallowed. To demonstrate the handler itself, remove the explicit denominator check and test a zero denominator.

14. Returning Values with OUT and INOUT Parameters

PostgreSQL supports parameter modes that influence how values enter or leave a routine.

IN parameters provide input values. OUT parameters provide output values. INOUT parameters provide an initial input value that can also be returned as output.

For functions, OUT parameters can help define returned columns or values. Procedures can also use output parameters, subject to PostgreSQL's supported syntax and version. When calling a procedure with output parameters, use the CALL syntax required by your PostgreSQL version and client.

A simple function can return multiple output columns:

CREATE OR REPLACE FUNCTION calculate_stats(
a INTEGER,
b INTEGER,
OUT total INTEGER,
OUT difference INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
total := a + b;
difference := a - b;
END;
$$;

SELECT * FROM calculate_stats(10, 4);

Expected result:

total | difference
-------+-----------
14 | 6

OUT parameters make the result shape explicit. For straightforward cases, RETURNS TABLE or RETURNS a composite type may be easier to understand. Choose one consistent interface for callers.

15. Security and Permissions

Database routines execute with security behavior that matters when multiple users or application roles have different permissions.

By default, PostgreSQL functions and procedures use SECURITY INVOKER behavior: database access is checked using the privileges of the invoking user. SECURITY DEFINER changes the execution privilege context to that of the routine's owner.

SECURITY DEFINER can be useful for tightly controlled operations, but it must be designed carefully. A routine that runs with elevated privileges can expose data or permit actions the caller could not otherwise perform.

When using SECURITY DEFINER:
- Use a trusted owner with only the necessary privileges.
- Set a safe search_path, preferably to trusted schemas, and avoid relying on schemas writable by untrusted users.
- Schema-qualify sensitive objects where appropriate.
- Restrict who can execute the routine.
- Review dynamic SQL carefully and use format('%I', ...) for identifiers and USING parameters for values when building dynamic statements.

PostgreSQL grants EXECUTE on routines to PUBLIC by default in many creation scenarios. Review and adjust privileges according to the project's security policy.

For example, to restrict execution of a function:

REVOKE EXECUTE ON FUNCTION add_numbers(INTEGER, INTEGER) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION add_numbers(INTEGER, INTEGER) TO app_role;

Replace app_role with an existing database role. These statements change permissions; run them only in a database where you have reviewed the intended access policy.

16. Practical Design and Performance Guidelines

Use database routines when they make logic easier to reuse, secure, maintain, or execute consistently.

**Prefer set-based SQL:** Use one INSERT, UPDATE, DELETE, or SELECT when it expresses the operation clearly. Avoid row-by-row loops when a set-based statement can do the job.

**Keep routines focused:** A routine should have a clear purpose and a well-defined input and output contract.

**Validate inputs:** Decide how to handle NULL values, invalid ranges, missing records, and duplicate requests.

**Understand volatility:** PostgreSQL functions can be declared IMMUTABLE, STABLE, or VOLATILE. These declarations communicate assumptions about whether a function's result depends on arguments, database state, or other changing factors. Do not mark a function IMMUTABLE unless that promise is true.

**Use realistic testing:** Test success paths, invalid inputs, NULL values, permissions, concurrency, and rollback behavior.

**Inspect performance when needed:** EXPLAIN helps inspect a query plan. EXPLAIN ANALYZE executes the statement and reports actual execution measurements, so be careful when using it on statements that modify data.

**Avoid unnecessary complexity:** Not every repeated query needs a stored routine. Application-side logic may be easier to test or maintain in some architectures. Choose based on consistency, ownership, permissions, and operational needs.

17. Common Use Cases

Stored functions and procedures are useful in several kinds of systems.

**Order processing:** A procedure can validate an order, update its state, and create related records as part of a controlled workflow.

**Reporting:** A function can return filtered records or calculated metrics for dashboards and reports.

**Business calculations:** A function can centralize calculations used by multiple queries or application features.

**Data maintenance:** A procedure can encapsulate a repeatable maintenance task, such as archiving eligible rows or processing a batch.

**Permission-controlled operations:** A carefully designed SECURITY DEFINER routine can expose a narrow operation without granting broad table access.

A database routine is not automatically faster or more secure than application code. Its value comes from a good interface, appropriate transaction behavior, correct permissions, and maintainable implementation.

18. Hands-On Practice

Complete these exercises in a PostgreSQL test database.

**Exercise 1 — Function basics:** Create a function named multiply_numbers that accepts two INTEGER parameters and returns their product. Test it with 6 and 7.

**Exercise 2 — Conditional logic:** Create a function named describe_age that accepts an integer age. Return 'Invalid age' for NULL or negative values, 'Minor' for values below 18, and 'Adult' otherwise.

**Exercise 3 — Return rows:** Create a function that returns all products whose prices are below a supplied maximum. Use RETURNS TABLE and RETURN QUERY.

**Exercise 4 — Procedure:** Create a procedure that inserts a row into a test audit table after validating that a required message is not NULL or empty.

**Exercise 5 — Error handling:** Modify an arithmetic function so it raises a clear exception for invalid input. Decide whether each error should be handled inside the routine or passed to the caller.

**Exercise 6 — Design choice:** For each task below, choose a function or procedure and explain why:
- Return a calculated tax amount.
- Return a set of matching customer rows.
- Execute a named batch-processing workflow that may manage transactions.

Test normal inputs, boundary values, NULL values, and invalid inputs. Do not test destructive operations against production data.

19. Knowledge Check

**Question 1:** Which command invokes a PostgreSQL stored procedure?

A. SELECT
B. CALL
C. EXECUTE FUNCTION
D. RETURN

**Answer:** B. CALL invokes a procedure.

**Question 2:** Which declaration specifies the return type of a function?

A. RETURNS
B. OUTPUT
C. RESULT
D. TYPEOF

**Answer:** A. RETURNS specifies the function's return type.

**Question 3:** Which language commonly provides variables, IF statements, loops, and exception handling in PostgreSQL?

A. HTML
B. PL/pgSQL
C. CSS
D. JSON

**Answer:** B. PL/pgSQL is PostgreSQL's procedural language.

**Question 4:** Can an ordinary PostgreSQL function issue COMMIT to end the current transaction?

A. Yes, always
B. Yes, if it returns VOID
C. No
D. Only when it contains a loop

**Answer:** C. Functions cannot execute transaction-control statements such as COMMIT or ROLLBACK.

**Question 5:** What does RETURN QUERY do inside a PL/pgSQL function that returns a set of rows?

A. Deletes the result rows
B. Appends query results to the function's result set
C. Creates a permanent table
D. Commits the transaction

**Answer:** B. RETURN QUERY appends rows produced by the query to the function's result.

**Question 6:** What is a key risk of SECURITY DEFINER routines?

A. They cannot execute SQL
B. They always run more slowly
C. They can expose elevated privileges if poorly designed
D. They cannot accept parameters

**Answer:** C. They run with the owner's privilege context and require careful permission and search_path design.

20. Interview Questions and Answers

**Q1. What is a stored function?**

A stored function is a named database routine created with CREATE FUNCTION. It accepts parameters and returns a declared result, which may be a scalar value or a set of rows.

**Q2. What is a stored procedure?**

A stored procedure is a named routine created with CREATE PROCEDURE and invoked using CALL. It is commonly used to perform database operations or orchestrate a workflow.

**Q3. What is the difference between a function and a procedure in PostgreSQL?**

A function returns a declared result and can be used in permitted SQL expressions or queries. A procedure is invoked using CALL and can perform operations without returning a function-style result. Eligible procedures can control transactions, whereas functions cannot issue COMMIT or ROLLBACK.

**Q4. What is PL/pgSQL?**

PL/pgSQL is PostgreSQL's procedural language. It extends SQL with local variables, conditions, loops, and exception handling.

**Q5. What is the difference between RETURNS INTEGER and RETURNS TABLE?**

RETURNS INTEGER declares a scalar integer result. RETURNS TABLE defines named output columns and allows a function to return multiple rows.

**Q6. What does RETURN QUERY do?**

RETURN QUERY executes a query and appends its result rows to the result set of a set-returning PL/pgSQL function.

**Q7. Can a function modify database data?**

Yes. A PostgreSQL function can perform data modifications when permitted by its language and behavior. However, it cannot independently commit or roll back the surrounding transaction.

**Q8. When can a procedure use COMMIT or ROLLBACK?**

Only in eligible execution contexts. For example, transaction control may be used by a procedure called outside an explicit transaction block, subject to PostgreSQL's restrictions.

**Q9. What is SECURITY DEFINER?**

It makes a function or procedure execute with the privilege context of its owner rather than the invoking user. It must be secured carefully to prevent privilege escalation.

**Q10. When should you use a function instead of a procedure?**

Use a function when the caller needs a returned value or result set that can be queried. Consider a procedure for a named operation or workflow, particularly when eligible transaction control is required.

**Q11. What is exception handling in PL/pgSQL?**

An EXCEPTION block catches specified errors and lets the routine respond to them. Handlers should be deliberate because catching an error without appropriate action can hide a failure.

**Q12. Are stored routines always faster than application code?**

No. Performance depends on the SQL, query plans, data volume, network overhead, and implementation. Measure the actual workload rather than assuming a stored routine is faster.

21. Lesson Summary

In this lesson, you learned how to create reusable PostgreSQL functions and stored procedures.

Key concepts:
- CREATE FUNCTION defines a function; SELECT commonly invokes it.
- CREATE PROCEDURE defines a procedure; CALL invokes it.
- PL/pgSQL adds variables, conditions, loops, and exception handling.
- RETURNS TABLE and RETURN QUERY support functions that return rows.
- Procedures can control transactions only in eligible contexts; ordinary functions cannot commit or roll back.
- Permissions, SECURITY DEFINER behavior, validation, and transaction boundaries are essential design considerations.
- Prefer simple, set-based SQL where possible and test routines against realistic scenarios.

Before moving on, make sure you can create and call a function, return multiple rows, explain the function/procedure distinction, and identify the security risks of elevated-privilege routines.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Calling a procedure with SELECT

Why it happens: Functions and procedures both contain reusable database logic, so their invocation syntax can be confused.

Incorrect Approach
sql
sql
SELECT record_payment(101, 250.00);
Correct Solution
sql
sql
CALL record_payment(101, 250.00);

Key takeaway: Use CALL for procedures. Use SELECT to invoke functions that return values or result sets.

#2 Using COMMIT or ROLLBACK inside a function

Why it happens: Developers assume a function can manage transactions in the same way as a procedure.

Incorrect Approach
sql
sql
CREATE OR REPLACE FUNCTION finish_work()
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE jobs SET status = 'done';
    COMMIT;
END;
$$;
Correct Solution
sql
sql
CREATE OR REPLACE PROCEDURE finish_work()
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE jobs SET status = 'done';
END;
$$;
 
CALL finish_work();

Key takeaway: Ordinary PostgreSQL functions cannot perform transaction control. Procedures may do so only when invoked in an eligible context. The corrected example intentionally leaves transaction management to the caller.

#3 Ignoring NULL inputs

Why it happens: SQL comparisons involving NULL produce unknown rather than an ordinary true or false result.

Incorrect Approach
sql
sql
IF score < 50 THEN
    RETURN 'Fail';
ELSE
    RETURN 'Pass';
END IF;
Correct Solution
sql
sql
IF score IS NULL THEN
    RETURN 'Unknown';
ELSIF score < 50 THEN
    RETURN 'Fail';
ELSE
    RETURN 'Pass';
END IF;

Key takeaway: Handle NULL explicitly when it has a meaningful business interpretation. Otherwise, missing values may be classified incorrectly.

#4 Using RETURN QUERY without a set-returning result declaration

Why it happens: RETURN QUERY is mistaken for a general replacement for RETURN in every function.

Incorrect Approach
sql
sql
CREATE FUNCTION get_numbers()
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY SELECT 1;
END;
$$;
Correct Solution
sql
sql
CREATE FUNCTION get_numbers()
RETURNS TABLE (number INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY SELECT 1;
END;
$$;

Key takeaway: RETURN QUERY is used in PL/pgSQL functions designed to return a set of rows, such as functions declared with RETURNS TABLE or RETURNS SETOF.

#5 Granting elevated execution privileges without reviewing security

Why it happens: A routine may be created to simplify access, but its privilege context and object resolution are overlooked.

Incorrect Approach
sql
sql
CREATE OR REPLACE FUNCTION privileged_action()
RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    -- Sensitive database operations
    NULL;
END;
$$;
Correct Solution
sql
sql
-- Use SECURITY DEFINER only when necessary.
-- Set a safe search_path and qualify sensitive objects.
-- Restrict EXECUTE privileges to the intended role.
REVOKE EXECUTE ON FUNCTION privileged_action() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION privileged_action() TO app_role;

Key takeaway: The permission statements are illustrative and require an existing function and role. Review ownership, schema resolution, object permissions, and the intended callers before deploying elevated-privilege routines.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

  • Functions are invoked through expressions such as SELECT and return a value or a result set according to their declared return type.
  • Procedures are invoked using CALL and can perform operations without returning a function-style result value.
  • In PostgreSQL, transaction control is available inside eligible procedures and DO blocks, but not ordinary functions.
  • PL/pgSQL supports variables, conditional statements, loops, and exception handling.
  • Server-side routines improve code reuse but should be designed with permissions, transaction boundaries, and performance in mind.
Was this lesson helpful?
Found an error? Report an issue