What are PostgreSQL functions and when do I have to use them?

Question

I want to know what PostgreSQL functions are.
When do I have to write them?
How can I write them?
And how can I call them?

Answer 1

Definition, from wikipedia:

A stored procedure is a subroutine available to applications that access a relational database system.

Advantages of stored procedures in general, from wikipedia:

Overhead: Because stored procedure statements are stored directly in the database, they may remove all or part of the compilation overhead that is typically required in situations where software applications send inline (dynamic) SQL queries to a database. (...)

Avoidance of network traffic: A major advantage with stored procedures is that they can run directly within the database engine. In a production system, this typically means that the procedures run entirely on a specialized database server, which has direct access to the data being accessed. The benefit here is that network communication costs can be avoided completely. This becomes particularly important for complex series of SQL statements.

Encapsulation of business logic: Stored procedures allow programmers to embed business logic as an API in the database, which can simplify data management and reduce the need to encode the logic elsewhere in client programs. (...)

Delegation of access-rights: In many systems, stored procedures can be granted access rights to the database that users who execute those procedures do not directly have.

Some protection from SQL injection attacks: Stored procedures can be used to protect against injection attacks. Stored procedure parameters will be treated as data even if an attacker inserts SQL commands. (...)

In PostgresSQL stored procedures are called user defined functions. Definition example:

CREATE FUNCTION somefunc(quantity integer) RETURNS integer AS $$
DECLARE
    myvariable integer := 2;
BEGIN   
    RETURN quantity * myvariable;
END;
$$ LANGUAGE plpgsql;

(You can use other languages to define stored functions in PostgreSQL)

Calling example:

SELECT somefunc(100);

More info: http://www.postgresql.org/docs/9.1/static/server-programming.html

Answer 2

PostgreSQL runs stored procedures in more than a dozen programming languages, including Java, Perl, Python, Ruby, Tcl, C/C++, and its own PL/pgSQL, which is similar to Oracle's PL/SQL.

The use of stored procedure depends on your needs and depends on the logic of your program, in my opinion stored procedure are useful only in some cases and not ever...

I've used stored procedure in a multi database server application, in this case the use of stored procedure could be extremely useful for example in the case you have a query that need to be modified for run in another database server type, in that case you can write a stored procedure in each database server and call it from your program being sure that it run and retrieve the wanted resultset without any changes in the client code.

To learn how to create stored procedure in PostgreSQL refer to this page of the documentation.