Difference between a procedure and a function.

A procedure and a function are subroutines in databases and programming, but there are key differences between them:

• Function always returns a value and can be used in expressions, for example, in SELECT.
• Procedure can perform actions (such as modifying data), but is not required to return a value directly.

Example in SQL (PL/pgSQL):

-- Function returns the sum of two numbers
CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- Procedure inserts a record into a table
CREATE PROCEDURE insert_user(name TEXT) AS $$
BEGIN
  INSERT INTO users(username) VALUES (name);
END;
$$ LANGUAGE plpgsql;

Functions are typically used for calculations and return results, while procedures are used to perform operations, often with side effects.