Dev.to

DAY-8 PSQL, Difference Function vs. Procedure

Difference Between Function and Procedure in PSQL Example of a Function in PSQL A function that returns the sum of two numbers: CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$ BEGIN RETURN a + b; END; $$ LANGUAGE plpgsql; -- Calling the function SELECT add_numbers(5, 10); Example of a Procedure in PSQL A procedure that inserts data into a table: CREATE PROCEDURE insert_employee(emp_name TEXT, emp_salary NUMERIC) LANGUAGE plpgsql AS $$ BEGIN INSERT INTO employees (name, salary) VALUES (emp_name, emp_salary); END; $$; -- Calling the procedure CALL insert_employee('John Doe', 50000); Key Takeaways Functions return a value and can be used in queries (SELECT function_name()). Procedures do not return values and are used for actions like modifying data (CALL procedure_name()). Procedures allow transaction control, but functions do not. Use functions for computations and procedures for business logic that modifies data. What is a Function in PostgreSQL (PSQL)? A function in PostgreSQL is a reusable SQL block that returns a value. It can take input parameters, execute SQL statements, and return a single value, a table, or a composite type. Key Features of a Function in PSQL ✅ Returns a Value – Can return a scalar (integer, text, etc.), a table, or a composite type. ✅ Used in Queries – Can be used in SELECT, WHERE, and JOIN clauses. ✅ Does Not Allow Transaction Control – Functions cannot have COMMIT or ROLLBACK inside them. ✅ Can Be Written in Multiple Languages – PL/pgSQL, SQL, Python (PL/Python), etc. Key Takeaways Functions must return a value. They can be used in SQL statements like SELECT. Cannot perform COMMIT or ROLLBACK operations. Useful for calculations and retrieving data. What is a Procedure in PostgreSQL (PSQL)? A procedure in PostgreSQL is a stored block of SQL code that executes a set of statements without returning a value. Unlike functions, procedures are mainly used for executing operations that modify data, such as INSERT, UPDATE, DELETE, and they support transaction control (COMMIT, ROLLBACK). Key Features of a Procedure in PSQL ✅ Does NOT return a value – Unlike functions, procedures do not return values. ✅ Executed with CALL – Procedures are invoked using the CALL statement. ✅ Supports Transaction Control – Procedures can have COMMIT and ROLLBACK. ✅ Used for Business Logic – Typically used for batch processing, data modifications, and complex Example: A Procedure with Transaction Control When to Use a Procedure? When you need transaction control (COMMIT, ROLLBACK). When performing batch updates or complex operations. When no return value is needed.

Apr 2, 2025 - 16:43

DAY-8 PSQL, Difference Function vs. Procedure

Difference Between Function and Procedure in PSQL

Example of a Function in PSQL

A function that returns the sum of two numbers:

CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- Calling the function
SELECT add_numbers(5, 10);

Example of a Procedure in PSQL

A procedure that inserts data into a table:

CREATE PROCEDURE insert_employee(emp_name TEXT, emp_salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO employees (name, salary) VALUES (emp_name, emp_salary);
END;
$$;

-- Calling the procedure
CALL insert_employee('John Doe', 50000);

Key Takeaways

Functions return a value and can be used in queries (SELECT function_name()).
Procedures do not return values and are used for actions like modifying data (CALL procedure_name()).
Procedures allow transaction control, but functions do not.
Use functions for computations and procedures for business logic that modifies data.

What is a Function in PostgreSQL (PSQL)?

A function in PostgreSQL is a reusable SQL block that returns a value. It can take input parameters, execute SQL statements, and return a single value, a table, or a composite type.

Key Features of a Function in PSQL

✅ Returns a Value – Can return a scalar (integer, text, etc.), a table, or a composite type.
✅ Used in Queries – Can be used in SELECT, WHERE, and JOIN clauses.
✅ Does Not Allow Transaction Control – Functions cannot have COMMIT or ROLLBACK inside them.
✅ Can Be Written in Multiple Languages – PL/pgSQL, SQL, Python (PL/Python), etc.

Key Takeaways

Functions must return a value.
They can be used in SQL statements like SELECT.
Cannot perform COMMIT or ROLLBACK operations.
Useful for calculations and retrieving data.

What is a Procedure in PostgreSQL (PSQL)?

A procedure in PostgreSQL is a stored block of SQL code that executes a set of statements without returning a value. Unlike functions, procedures are mainly used for executing operations that modify data, such as INSERT, UPDATE, DELETE, and they support transaction control (COMMIT, ROLLBACK).
Key Features of a Procedure in PSQL

✅ Does NOT return a value – Unlike functions, procedures do not return values.
✅ Executed with CALL – Procedures are invoked using the CALL statement.
✅ Supports Transaction Control – Procedures can have COMMIT and ROLLBACK.
✅ Used for Business Logic – Typically used for batch processing, data modifications, and complex

Example: A Procedure with Transaction Control

When to Use a Procedure?

When you need transaction control (COMMIT, ROLLBACK).
When performing batch updates or complex operations.
When no return value is needed.