0

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.

2
  • 2
    Contrast with "Table Valued Function" which is not in the list of terms, it returns a full rowset. "required" is a mistake, you can have optional parameters or none at all. And I'm unsure if this is a tutorial or product-specific: it has weird definitions like "User-defined Function – An application-specific SQL callable scalar or aggregate function written in C." and "Virtual Table – An SQL table that is defined through a set of application-specific C functions that conform to a particular interface specification" and "Snapshot Isolation – When a snapshot of the database is taken, ..... Jul 27, 2022 at 21:20
  • 1
    ..... an instance of the database is frozen and concurrent reads are allowed to occur on that snapshot." and "Encryption – The encoding of data so that it cannot be understood by a human reader" and "Deterministic – An attribute of a section of code whereby the limit on the time required to execute the code is known, or determined, ahead of time" All of which are either down-right incorrect or only correct on specific platforms. Jul 27, 2022 at 21:20

2 Answers 2

2

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.

1
  • Ok, I see. Can you suggest any resources for learning basic terminology? This was an example, but I often find myself similarly confused. And I have no way of knowing if I am looking at a good or a sloppy definition.
    – Stonecraft
    Jul 27, 2022 at 21:30
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.