Actually, the docs explain better, no modification + same result, so it's actually immutable and idempotent.

IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values.

Idempotent is still the wrong word. Idempotent means that the function can be composed multiple times with no effect. The term I think you are looking for is that it must be a pure function.

"Idempotent means that the function can be composed multiple times with no effect."

Close, but not quite. The first application may have an effect. The second application and so on may not.

For example, the HTTP spec defines GET as idempotent. An initial GET request for a URI may have a side-effect, but, per the spec, subsequent requests return the same content and have no further side-effects.

Edit - reading the GP, I want to clarify that an immutable function in PostgreSQL is idempotent, since the N+1 applications have no side effect and return the same result as the 1st. Also, the 1st has no side effect.

    per the spec, subsequent requests return the same content

That's not the sense in which HTTP GET is idempotent. The spec says nothing about returning the same content. In fact, servers are free to return uncacheable content that changes on every GET request, and many do so (for example, a page that contains the current time is a valid HTTP GET response). The HTTP definition of idempotence only applies to side-effects on the server.

You can formally reconcile this definition with the mathematical definition as follows: an HTTP request is expected to cause some operation on the server (an operation is a function with the same domain and range). This operation takes the server from one state X to a new state Y. Since HTTP GET is supposed to be idempotent, if you make a GET request to a server in state X, it is free to transition into some state Y != X, but if you reissue the request to the server in state Y, it should remain in state Y.

So basically, an IMMUTABLE function in PostgreSQL is trivially idempotent in terms of operations on the server, in that it can have no effect at all on the state of the database. But as far as mathematical properties of the function go, it does not need to be idempotent.

I agree, and I misspoke on the point which you corrected me on.

eh!?

A pure function is an idempotent function which has no side-effects. For example no logging.

You can have an impure idempotent function: a function always producing the same output for given input but that is impure because it has side-effects, like logging or sending a heartbeat on the network or whatever.

But you cannot have a pure non-idempotent function.

Any idempotent function --and hence any pure function-- shall always be giving the same output given some input (and hence can be cached etc.) but the idempotent function can have side-effects (but not side-effects that would modify the "idempotentness" of the function itself.

A pure function does not need to be idempotent. For example f(x) = x * 2 is a pure function, but applying it twice multiplies by four instead of two.

In computer science, another sense of the word idempotent has arisen, which relates to operations on a mutable identity. In this sense, you might say "UPDATE table SET col1=0 WHERE id=25" is an idempotent operation on databases: applying it to a database in some state results in a new database state, but applying it again to that new database state results in that same database state again (this is the sense in which HTTP GET requests are idempotent, for example).

An IMMUTABLE function is a pure function, at least with respect to the database, since it cannot mutate the database which is the side effect we care about. It is not necessarily idempotent: given that f is an IMMUTABLE function, and f(2) == 4, I can't say for sure that f(4) == 4.