Why are scalar functions special? A case study of trying to understand database terminology as a beginner

Question

I am trying to learn the basics of databases, but I am finding the terminology especially impenetrable compared to other things I have learned. Most of the database tutorials and documentation I have looked at explain everything in terms of other database terminology. Most examples only show code but not actual output, so I am often unclear as to what the expected output is. I am usually even less clear on what the intended use case is.

For example, take the term "scalar function" which I recently come across. It is defined here similarly to other definitions I found.

https://raima.com/database-terminology/:

Scalar Function – Either a built-in SQL function or a user-defined function that returns a single value computed only from the values of any required arguments at the time the function is called.

Why is that special? How is that different from just a function that outputs one value? Why is the fact that the arguments are "required arguments" a noteworthy distinction? Why, in practical terms, would it be important to specify that a scalar function is to be used?

I would appreciate both an answer to this specific question and references to database (preferably Postgres) learning materials that make a point to explain each technical term in the least technical way possible.

Answer 1

Scalar is just one type of functions a database can have. For example, in Postgres, in addition to a scalar function, one can define a function that returns a record (a tuple of scalars) and a function that returns a set of items (scalars or records), essentially a table.

Other DBMSes have similar options.

function that returns a single value computed only from the values of any required arguments at the time the function is called.

Why is the fact that the arguments are "required arguments" a noteworthy distinction?

That's just sloppy wording on their side. A scalar (or any other type of) function does not have to have any arguments, required or optional, its result doesn't have to depend on any of them, and it can always return the same value regardless of the arguments "at the time the function is called".

That's not the only occurrence of the sloppy wording. As mentioned in comments there's this:

An application-specific SQL callable scalar or aggregate function

though aggregate functions are scalar functions, because each of them returns a single value.

Answer 2

Why are scalar functions special?

A scalar functions is just a function that returns a "scalar" value, i.e. a single value; not a pair/tuple (record) or set of tuples (i.e. a relational table). PostgreSQL function can return any of these things. It's a general notion not specific to databases or SQL.

Why is it special?

IMO, it's because the return value of a scalar function is special (the simplest in terms of dimension), and the return value can be used in many places where a single value is expected. For example, if you are comparing the return value to a number in the WHERE clause, e.g.

WHERE my_func(...) > 5

or in a sub-query that expects a single value.

The general idea is that the parameters of many operators/functions (such as the > above) must have the same dimension. And scalar values are used a lot.