As in other programming languages, an SQL function must return a value. Conceptually, it is used to encapsulate business logic for later reuse. A function can also be used inside a query.
Let's look at an example function
Here we encapsulate business logic that we can then reuse. Overall, the concept of SQL functions is no different from functions in other languages.
CREATE OR REPLACE FUNCTION user_level(points integer)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$
SELECT CASE
WHEN points >= 1000 THEN 'gold'
WHEN points >= 500 THEN 'silver'
WHEN points >= 100 THEN 'bronze'
ELSE 'starter'
END;
$$;
SELECT id, points, user_level(points) AS level
FROM users;
Procedures are most often used for various jobs (for example, fixing corrupted data), migrations, or implementing simple workflows used by BAs (business analysts) that make no sense to add to the project's codebase.
It is important to understand that procedures do not return a value, so they are not used in SQL queries; instead, they are invoked with the keyword CALL.